tserio tserio - 1 year ago 72
SQL Question

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


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.

Answer Source

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;