Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Unanswered: Need help with a many-to-many relationship join

    Hi!

    I have 3 tables that form a many-to-many relationship in the exact same way as the example in the postgresql documentation at: PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Constraints

    The tables are (from the documentation):

    Code:
    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );
    
    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text,
        ...
    );
    
    CREATE TABLE order_items (
        product_no integer REFERENCES products,
        order_id integer REFERENCES orders,
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );
    So, by using this table structure I need to list all orders that contain one or more products. For example, say we have 3 orders with the following relationship:

    order#1 containing the products: apple, banana, mango.
    order#2 containing the products: grapes
    order#3 containing the products: apple, mango

    Now, how can I write a SQL-line to list all orders containing the products 'apple' and 'mango'? Ie. giving the result:

    order#1
    order#3


    Thanks in advance
    /codehunter

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT oi.order_id
      FROM products AS p
    INNER
      JOIN order_items AS oi
        ON oi.product_no = p.product_no
     WHERE p.name IN ('apple','mango') -- note 2 products listed
    GROUP
        BY oi.order_id
    HAVING COUNT(*) = 2 -- number of products listed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    9
    It works great, thank you!

    /codehunter

  4. #4
    Join Date
    Nov 2009
    Posts
    9
    A follow up question...
    what if I want to list all columns in the orders-table, not just the primary key?

    I thought I could figure this out but I failed

    /codehunter

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    SELECT p.*
    FROM ...

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Scratch that.

    Code:
    SELECT *
    FROM order_items
    WHERE order_id IN (
      SELECT oi.order_id
      FROM products AS p
      JOIN order_items AS oi
        ON oi.product_no = p.product_no
      WHERE p.name IN ('apple','mango') -- note 2 products listed
      GROUP BY oi.order_id
      HAVING COUNT(*) = 2
    )

  7. #7
    Join Date
    Nov 2009
    Posts
    9
    Thank you!

    I tried to include the rows from orders like: "SELECT oi.order_id,o.order_id,... " etc. but that gave me all sorts of errors

    /codehunter

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by codehunter View Post
    I tried to include the rows from orders like: "SELECT oi.order_id,o.order_id,... " etc. but that gave me all sorts of errors
    please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2009
    Posts
    9
    Quote Originally Posted by r937 View Post
    please show your query
    What I meant was that I needed the nested SELECT in order to get it to work (as artacus72 said in post #6). What I first tried was something like (dont remember exactly how I wrote it):

    Code:
    SELECT oi.order_id,o.shipping_address
    FROM products AS p, orders AS o
    JOIN order_items AS oi
    ON oi.product_no = p.product_no
    WHERE p.name IN ('apple','mango') -- note 2 products listed
    GROUP BY oi.order_id
    HAVING COUNT(*) = 2
    ...and that gave me errors. But with the nested SELECT it works fine!

    (sorry for a late answer)
    /codehunter

Posting Permissions

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