SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: query problem

  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query problem

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido, why the HAVING condition?
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, why the HAVING condition?
    Quote Originally Posted by pearl_123 View Post
    Hi to all,
    Now i want to get the product name from product table who has more product in order_detail table
    That's why

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, yeah, i understand

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank guys problem solved.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •