On a given day ’10-JAN-11’ which order has most number of products placed

Question

1.00/5 (1 vote)

See more:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where o.date_ordered='2011-01-10'' at line 2

this is the error i got and i am not getting sufficient output

What I have tried:

SQL

select o.order_id,p.product_id from orders o,products8 p,order_products8 t where o.order_id=t.order_id and p.product_id=t.product_id
where o.date_ordered='2011-01-10';

Posted 25-Feb-19 23:58pm

Member 14163078

Updated 26-Feb-19 0:45am

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 26-Feb-19 6:07am

did you try 2011/1/10 ?

Member 14163078 26-Feb-19 6:13am

yes

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-02-26T00:22:00

Solution 1

Why do you have two WHERE clauses?
And you should probably look at a JOIN instead of trying to select from multiple tables. I can't write it for you - I have no idea of your tables and how they are interrelated, but:

C#

SELECT a.Name, b.Address FROM Users a
JOIN Addresses b ON a.ID = b.User_ID
WHERE a.JoinDate >= '2018-01-01'

Posted 26-Feb-19 0:22am

OriginalGriff

Updated 26-Feb-19 0:37am

v2

Comments

Maciej Los 26-Feb-19 6:28am

You mean "WHERE" (not "WHILE") clauses...

OriginalGriff 26-Feb-19 6:37am

Oh C**k.
Fixed :thumbsup:

Maciej Los 26-Feb-19 6:47am

;)Have a nice day, OG (Paul)!

Maciej Los · Answer 2 · 2019-02-26T00:46:00

Assuming that order_products8 is a table with many to many relationships between orders and products8 tables,

- to get count of products

SQL

SELECT op.order_id, COUNT(op.product_id) CountOfProductsInOrder
FROM order_products8 AS op
GROUPBY op.order_id
WHERE op.date_ordered='2011-01-10';

- to get max number of products in order

SQL

SELECT dt.order_id, MAX(CountOfProductsInOrder) TheHigestNumberOfProductsInOrder
FROM (
-- use above query here
) AS dt
GROUPBY dt.order_id;