Page 1 of 1

How to find what foreign relations don't exist...

Posted: Mon Aug 14, 2006 12:19 pm
by Todd_Z
I have a table which contains property information, with a unique id.
I have a table with client information, which has a unique id.
I have a table where it I calculate how 'relevant' the property is to the client.

Is there a way, using one query to find out which property id and client id pairs don't exist in the relevant table?

Posted: Mon Aug 14, 2006 12:20 pm
by feyd
There's a post linked from Useful Posts detailing a query to find results without matches across tables.

Posted: Mon Aug 14, 2006 12:33 pm
by Todd_Z
It looks like that example has only two tables, whereas my situation uses three... I don't know how I can apply it.

Code: Select all

select property.id, client.id from property, client where ( select count(*) from relevance where property_id = property.id and client_id = client.id ) = 0;

I came up with that, but idk if that is the best way to do it, I feel like I don't need a subquery.