Hmm All or nothing..bit complicated sorry :confused:

[Deleted member 28156]

Hi ,
say I have a table:

video: id, title, artistname, countryid
that has the values

1, 'VideoA','Artsit123' ,4
2, 'VideoB', 'Artsit567' , 5
3, 'VideoC', 'Artsit167' ,6
4, 'VideoD', 'Artsit890' , 7
5, 'VideoE', 'Artsit468' , 8
6, 'VideoF', 'Artsit752' , 9

And another table country: id
that has the values:
1,4,5

Basically is there a query that can be written for me to check if ALL the countryids from the country table will return an artist, and if not can it let me know. For example obviously there is no countryid with 1 in the video table so Im not interested in that batch in the country table. I know I can just execute the query 3 times to see if a result is produced but what if the country table is massive (btw the country table is generated dynamically using xml). Any help on this would be very mch appreciated

 

[Michael J Ross]

So are you wanting to identify which country ids have artists and which don't?

Create a query using a left outer join between country and video tables on country id and use country id as a field from both tables in your query and group by it.

When you run the query where there are matches the countryid will show in both columns but where a counntry id doesnt exist in your video table it will return a blank field.

 

[Moniker]

Show only the countries with an associated artist:

SELECT country.id
FROM country LEFT JOIN video ON country.id = video.id
WHERE video.id Is Not Null;

Show only the countries without an associated artist:

SELECT country.id
FROM country LEFT JOIN video ON country.id = video.id
WHERE video.id Is Null;

To see how to make these, copy the SQL above, go to your DB, select Queries, make a new one in Design View, don't add any tables, and then in the top left, switch to SQL View and paste in the copied text. Go back to the top left and switch to Design View, and you'll see how it's done.