CODEWITHSUNDEEP
mysql
tserio
tserio

Reputation: 35

MYSQL Select all that do not equal something

Im having a bit of difficulty with one equation I can't seem to figure out or find on here. I am trying to do the following;

I have 2 tables, products and teamproducts. There foreign key is productid. I am trying to select the products from the products table that have not been registered to the teamproducts table. I am also using a teamcode that in teamproducts. An example below;

products Table (productid | productname)

1001 | product 1

1002 | product 2

1003 | product 3

1004 | product 4

1005 | product 5

etc

teamproducts Table (teamcode | productid)

teamcode1 | 1001

teamcode1 | 1002

What I want to do is select all the products that aren't in the teamproducts page (so in this example product 3 and on)

I have tried the following;

    SELECT productname FROM products p, teamproducts tp WHERE teamcode = teamcode1 AND p.productid != tp.productid

and other variations I have seen but have not come up with the right line. Any help please.

Upvotes: 1

Views: 52

Answers (4)

Jehad Keriaki
Jehad Keriaki

Reputation: 545

What you are looking for is LEFT JOIN, with an extra condition that eliminates the null results from the right table:

SELECT * FROM products 
LEFT JOIN teamproducts USING (productid) 
WHERE teamproducts.productid IS NULL; -- Add other conditions as needed

EDIT Adding 'teamcode' condition:

SELECT * FROM products 
LEFT JOIN teamproducts USING (productid) 
WHERE teamproducts.productid IS NULL 
AND teamproducts.teamcode = 'teamcode1'; -- Add other conditions as needed

More about JOIN, and different types of it: http://dev.mysql.com/doc/refman/5.7/en/join.html

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can use a not in clause 

select * from products
where id not in (select productid from teamproduct);

Upvotes: 0

Radin
Radin

Reputation: 21

I think you have to write teamcode1 between quotes. Something like this:

SELECT productname FROM products p, teamproducts tp WHERE teamcode = 'teamcode1' AND p.productid != tp.productid

Upvotes: 0

Arjan
Arjan

Reputation: 9874

Untested, but I think this should do the job.

SELECT products.productname
FROM products
LEFT JOIN teamproducts
  ON teamproducts.teamcode = 'teamcode1'
  AND teamproducts.productid = products.productid
WHERE teamproducts.productid IS NULL;

Upvotes: 1

Related Questions