WHERE x NOT IN (y)

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?

 

Try this

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

Thanks

 

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

 

select pid from product where exists(select pid from featured)

 

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?

 

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);

 

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.

 

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.

 

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!

 

as much as possible use exist instead of in. using exist is generally faster.