1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

WHERE x NOT IN (y)

Discussion in 'MySQL' started by Weirfire, Apr 4, 2007.

  1. #1
    The following query

    SELECT p.products_id FROM products p, featured f WHERE p.products_id != f.products_id

    is giving me the result

    12
    13
    14
    15
    11
    13
    14
    15
    11
    12
    14
    15



    The table featured contains 11,12 and 13 and the table products contains 11,12,13,14 and 15

    What I want to do is select all the elements in products which don't occur in featured. This should be a simple query but I just can't get it right.

    I've tried NOT IN, !=, <>, NOT(x LIKE y) and none of these have given me the desired outcome.

    Can anyone help me with this?
     
    Weirfire, Apr 4, 2007 IP
  2. ewriter

    ewriter Banned

    Messages:
    590
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try this

    select * from product where pid in(select pid from featured)

    Thanks
     
    ewriter, Apr 4, 2007 IP
    Weirfire likes this.
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #3
    I'm afraid that gives me this error on PHPMYADMIN

    #1064 - 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 'SELECT products_id FROM featured) LIMIT 0, 30' at line 1
     
    Weirfire, Apr 4, 2007 IP
  4. ewriter

    ewriter Banned

    Messages:
    590
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #4
    select pid from product where exists(select pid from featured)
     
    ewriter, Apr 4, 2007 IP
  5. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #5
    This was the exact query I inserted into PHPMYADMIN

    SELECT products_id FROM products WHERE exists(SELECT products_id FROM featured)

    and I still got an error.


    Do you realise I'm looking for the elements which are NOT in featured?
     
    Weirfire, Apr 4, 2007 IP
  6. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #6
    I'm just going to do 2 queries for now.

    First query

    $result = mysql_query("SELECT products_id FROM featured");

    while($row = mysql_fetch_array($result)){

    $where .= " products_id != " . $row['products_id'] . " AND ";

    }

    $where = " WHERE " . $where;
    $where = substr($where,0,strlen($where)-4);

    $result2 = mysql_query("SELECT * FROM products " . $where);
     
    Weirfire, Apr 4, 2007 IP
  7. druidelder

    druidelder Peon

    Messages:
    285
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #7
    In your original query, you are essentially joining each row in products to each row in featured as long as the productIDs don't match.

    In this MySQL syntax the WHERE clause is controlling how the rows are joined, not what information is returned.

    Here are some solutions:

    SELECT productID
    FROM products
    WHERE (SELECT COUNT(featured.productID) FROM featured WHERE featured.productID = products.productID) = 0

    OR

    SELECT products.productID FROM products LEFT JOIN featured ON products.productID = featured.productID WHERE featured.productID IS NULL

    OR

    SELECT products.productID FROM products LEFT JOIN featured USING (productID) WHERE featured.productID IS NULL

    I can't be sure that the first one works in MySQL (does in MS-SQL) and while functional, it likely takes more resources than the others.

    The second and third are the same except that since the joining fields are named the same you can use the USING clause instead of the ON clause.

    To recap, the equi-join (or inner join), which uses the ',' syntax, joins two tables on a one to one ratio if there is no where clause. So without the where clause, the data in your two tables would result in fifteen results (3 * 5). Also note, without the where clause an inner join is called a cross join. When the where clause is used with this join, that is the condition for the join. So you were creating a result from products for each row in featured where the productIDs did not match. So, 14 and 15 each had three results because there are three rows in featured that did not have that productID. 11, 12, and 13 each would return 2 results as there are two rows in featured for each of those where the productID does not match. That's a total of 12 results.

    A left join occurs before the where clause. It is called a left join because the controlling table is the one named furthest to the left if the query were written on a single line. In this case, a result row will be created for each row in the left (or products) table based on either the ON clause or the USING clause. This occur regardless of whether there is a corresponding row in the right table. Your results before the where look like this:

    p.productID = 11, f.productID = 11
    p.productID = 12, f.productID = 12
    p.productID = 13, f.productID = 13
    p.productID = 14, f.productID = null
    p.productID = 15, f.productID = null

    The where clause would then keep only those results where f.productID is null.
     
    druidelder, Apr 4, 2007 IP
  8. druidelder

    druidelder Peon

    Messages:
    285
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Another thing, the query:

    SELECT products_id FROM products WHERE exists(SELECT products_id FROM featured)

    would never work. The reason for this is that the EXISTS clause returns a 1 if there are results and 0 if there are none. Your subquery (SELECT products_id FROM featured) will alway return a 1 as long as there is data in features. To get it to work properly:

    SELECT products_id FROM products WHERE not exists (SELECT products_id FROM featured WHERE products.products_id = featured.products_id)

    Subqueries were not used in MySQL before 4.1. So, if your MySQL is 4.0 or lower you would have to use the join method anyway.
     
    druidelder, Apr 4, 2007 IP
    Weirfire likes this.
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    Thank you for these solutions. Your description of my problems seems to fit with the problems I've been getting and when I get back to work on Tuesday I'll let you know if these work.

    Thanks again for your time on this! :)


     
    Weirfire, Apr 7, 2007 IP
  10. JosefVirek

    JosefVirek Peon

    Messages:
    35
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #10
    as much as possible use exist instead of in. using exist is generally faster.
     
    JosefVirek, Apr 7, 2007 IP