Query problem

Databases
1

Hi to all,

I have two table one is product table with attribute P_ID,P_Name and second table is order_detail with attribute O_ID,P_ID P_ID here is foreign key from product table

P_ID P_NAME

1 ABC

2 DEF

3 XYZ

O_ID P_ID

1 1

2 1

3 1

4 3

5 3

6 2

Now i want to get the product name from product table who has more product in order_detail table for example

query should return

Product name ABC from product table has 3 records in order detail at the top then product name XYZ b/c this has two rows in order detail table and then product name DEF.

thanks

2 

SELECT
    product.P_ID
  , product.P_NAME
  , COUNT(*) AS numberofdetails
FROM product
INNER JOIN order_detail
ON product.P_ID = order_detail.P_ID
GROUP BY
    product.P_ID
  , product.P_NAME
HAVING COUNT(*) > 1
ORDER BY numberofdetails
3

guido, why the HAVING condition?

SELECT p.name
     , COUNT(o.o_id) AS ordered
  FROM products AS p
LEFT OUTER
  JOIN order_detail AS o
    ON o.p_id = p.p_id
GROUP
    BY p.name
ORDER
    BY ordered DESC
4

That’s why :slight_smile:

5

okay, yeah, i understand

i just figured that was due to english not being the primary language of the original question

:slight_smile:

6

thank guys problem solved.