CODEWITHSUNDEEP
mysqlsql
Edward144
Edward144

Reputation: 493

MySQL Check if row contains value from another table

I need to check if a row in a MySQL table contains a value stored in another table.

This is what I am trying right now:

SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE Column = '%');

This still finds rows which do contain the value in the second table. I have also tried exchanging EXISTS for LIKE and IN which both return syntax errors.

Edit:

Using the % Sign seems to cause an issue. Here are some screenshots of what happens when I use the percent sign: enter image description here

One of the values stored in the second table is 'Meath', the row containing 'Meath' is still selected. If I add 'Meath' instead of '%' then the row isn't returned: enter image description here

Upvotes: 2

Views: 8013

Answers (3)

Sam W
Sam W

Reputation: 599

SELECT * FROM table1 a
WHERE a.County NOT IN(SELECT County FROM table2)

This should match what you want.

Edit: if you need to only disregard certain values from table2, you could replace the second line with this

WHERE a.County NOT IN(SELECT County FROM table2 WHERE County IN('county1', 'county2'))

Upvotes: 1

Maxson Ndlovu
Maxson Ndlovu

Reputation: 207

SELECT * FROM table1
WHERE column NOT IN (SELECT column FROM table2 
              WHERE Column = '%');

Upvotes: 0

M.Ali
M.Ali

Reputation: 69584

You need to define some sort of relation between these two queries, for example 

SELECT * FROM table1 
WHERE NOT EXISTS (SELECT * 
                  FROM table2 
                  WHERE Column = '%'
                    AND table1.Column = table2.Column);  --<-- Relate two queries

Each row returned from the Outer query will be checked for the condition you define in the where clause of the inner query, this relates the two queries else MySQL has no way of knowing how to check the existence of each row being returned by the outer query and it will return back nothing.

Upvotes: 0

Related Questions