检查数据是否存在于多个表中 [英] Check if data exists across multiple tables

问题描述

我目前正在MySQL内部运行此查询，以检查指定值在与它们关联的表中是否存在
。

 选择COUNT（artist.artist_id），COUNT（album.album_id），COUNT（tracks.track_id）
来自艺术家，专辑，曲目WHERE artist.artist_id = 320295 OR album.album_id = 1234或tracks.track_id = 809 
  

我从运行此查询得到的结果全部为1，这意味着WHERE子句为true。为了进一步检查查询的可靠性，我将tracks.track_ = 809更改为802，我知道这不匹配。但是，显示的结果仍然全部为1，这意味着即使我有意插入了一个不匹配的值，它们也都已成功匹配。

如何获取在同一查询中显示1表示匹配，显示0表示没有匹配？

编辑：我插入了运行查询的图像

解决方案

此处要做的是对三个表的联接。当您查看此 SQL提琴时，您会看到会发生什么。 / p>

在第一个选择中，我省略了 count ，以显示联接的工作方式。如@RayPaseur所建议的，当您将where子句从或修改为和时，您还可以看到结果集的变化。 。

我想，您想要的实际上是三个单独的查询

 选择艺术家作为类型，从艺术家
选择count（artist_id）作为
，其中artist_id = 320295 
 union 
选择专辑，count（album_id）
从专辑
中，其中album_id = 1234 
 union 
从轨道
中选择'track'，count（track_id）
其中track_id = 809 
  

变为

  TYPE COUNT 
艺术家1 
专辑1 
曲目1 
  

现在，当您将 track_id = 809 更改为 track_id = 802 ，您将得到

  TYPE COUNT 
位艺术家1 
专辑1 
曲目0 
  

。

SQL Fiddle 进行播放。

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

解决方案

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.

这篇关于检查数据是否存在于多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！