#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Multiple IN vs. JOIN in SQL


    Hello. I was trying to write
    Code:
    SELECT name
    FROM   items
    WHERE  id IN (SELECT cartitems.item_id
                  FROM   carts
                         INNER JOIN cartitems
                                 ON cartitems.cart_id = carts.id
                  WHERE  carts.customer_id = 750)
    but instead I came up with
    Code:
    SELECT name
    FROM   items
    WHERE  id IN (SELECT item_id
                  FROM   `cartitems`
                  WHERE  cart_id IN (SELECT id
                                     FROM   `carts`
                                     WHERE  customer_id = 750))
    Gives the same results! Is there something wrong or inappropriate with it? For example may only work in MySql?
    Thank you
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    why not simply join "items" with "carts" and "carts" with "cartitems"? That's what joins are for: aggregating associated data.

    Code:
    SELECT DISTINCT
    	items.name
    FROM 
    	items
    	JOIN
    		cartitems ON items.id = cartitems.item_id
    	JOIN
    		carts ON cartitems.cart_id = carts.id
    WHERE
    	carts.customer_id = 750
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Jacques1
    why not simply join "items" with "carts" and "carts" with "cartitems"?
    you're on the right track, but of course items don't relate to carts at all

    the origin of this question was an example in a book to show the IN (subquery) construction

    of course there are sql equivalents, but that wasn't the point of the example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo