ChrisPasa ChrisPasa - 5 months ago 11
SQL Question

Select Statement to return TaskID if column Exists in another table

I have two tables.

First table is called task the second table is named countries.

My task table

ID TaskID Country CustomerID
------------------------------------------
1 213 china 22
2 213 USA 24
3 213 china 26
4 214 Canada 28


Countries table

ID Country
---------------
1 USA
2 Japan
3 England


I need a select statement that returns all task ID's that doesnt have its country i the countries table.
In this case I would need to return TASKID: 214 because canada is not in the countries table. I would not get TASKID: 213 because USA is in the countries table.

Answer

Try this:

SELECT
   TaskID
FROM
   Task T LEFT OUTER JOIN Countries C ON (T.COUNTRY = C.COUNTRY)
GROUP BY
    TaskID
HAVING
    COUNT(C.ID) = 0
Comments