Human Human - 5 years ago 149
PHP Question

Check if data exists across multiple tables

I am currently running this query inside MySQL to check if the specified values exists
within the table associated with them.

SELECT COUNT(artist.artist_id), COUNT(album.album_id), COUNT(tracks.track_id)
FROM artist, album, tracks WHERE artist.artist_id = 320295 OR album.album_id = 1234 OR tracks.track_id = 809


The result I get from running this query is all 1, meaning that all the statements after the WHERE clause is true. To further check the query's reliability, I changed the tracks.track_ = 809 to 802, which I know does not match. However the results displayed are still all 1, meaning that they were all successfully matched even when I purposefully inserted a value which would not have matched.

How do I get it to show 1 for a match and 0 for no matches within the same query?

EDIT: I have inserted an image of the query running

enter image description here

Answer Source

What you do here is a join over three tables. You can see what happens, when you look at this SQL Fiddle.

In the first select, I have left out the count, to show how the join works. You can also see how the result set changes, when you modify the where clause from or to and as @RayPaseur suggested.

I guess, what you want, is really three separate queries

select 'artist' as type, count(artist_id) as count
from artist
where artist_id = 320295
union
select 'album', count(album_id)
from album
where album_id = 1234
union
select 'track', count(track_id)
from tracks
where track_id = 809

which becomes

TYPE    COUNT
artist  1
album   1
track   1

Now, when you change track_id = 809 to track_id = 802, you will get

TYPE    COUNT
artist  1
album   1
track   0

as a result.

SQL Fiddle for playing.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download