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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

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

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There's a post linked from Useful Posts detailing a query to find results without matches across tables.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
Post Reply