CODEWITHSUNDEEP
sqlsql-server
Pete O'Connell
Pete O'Connell

Reputation: 143

Is it possible to check for a record in 1 table and if it does not exist check another?

I'm wondering if it's possible to check to see if a row exists in a table, if it does not I want to see if it exists in another. I am happy to either know if there is simply a row or the actual row details.

My example is as follows:

I have a list of players, they can be authorised to be in a team or be temporarily in a team. I've got 2 tables which are identical in data structure:

PlayerList
id, team_ref, player_ref

PlayerListTemp
id, team_ref, player_ref

I want to first check if they are in the temp table, if not check to see if they are in the normal table. If neither exist I want to allow them to be picked.

Upvotes: 1

Views: 43

Answers (1)

Jamiec
Jamiec

Reputation: 136104

EXISTS is your friend

Assuming your selecting from a table (whatever in my example) which contains the player player_ref:

SELECT * 
FROM wherever
WHERE NOT EXISTS(SELECT 1 FROM PlayerList WHERE player_ref=whatever.player_ref)
AND NOT EXISTS(SELECT 1 FROM PlayerListTemp WHERE player_ref=whatever.player_ref)

Upvotes: 4

Related Questions