如何使用“where”连接两个表

Question

我来解释我的问题。我有这个表：

      artist                          artist_pic
-------------------       ---------------------------------------
| id | name | age |       | id_artist | picUrl | picDescription |
-------------------       ---------------------------------------
   |                          /\
   ----------------------------

每一位艺术家都有很多照片。

这是我的查询(它是有效的)

SELECT a.*, ai.* 
     FROM artist a INNER JOIN artist_pic ai on ai.id_artist = a.id  
         where ai.id_artist = '" + idArtist + "' and ai.elemento = 'artist'

现在我应该“添加”另一个名为artist_video的表

      artist                          artist_video
-------------------       ---------------------------------------
| id | name | age |       | id_artist | video| videoDescription |
-------------------       ---------------------------------------
   |                          /\
   ----------------------------

我试过这样的东西，但是我没有得到正确的记录

SELECT a.*, ai.*, av.* 
   FROM artist a 
       INNER JOIN artist_pic ai on ai.id_artist = a.id  
       INNER JOIN artist_video av on av.id_artist = a.id
         where ai.id_artist = 2 and ai.elemento = 'artist' and av.id_artist = 
          2 and av.elemento = 'artist'

例如，我应该得到的是：

michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family

但现在我得到的是这个(多次相同的记录)

michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family
michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family
michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family
michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family
michael jackson   Img/5345.jpg     concert      video/5345.avi     concert
michael jackson   Img/3453.jpg     family       video/2344.avi     family

Answer 1

我猜你想要所有的原始唱片，加上那些有视频的。如果是这样，这可能会解决您的问题：

SELECT a.*, ai.*, av.* 
FROM artist a INNER JOIN
     artist_pic ai
     on ai.id_artist = a.id and ai.elemento = 'artist' LEFT JOIN
     artist_video av
     on av.id_artist = a.id and av.elemento = 'artist'
where a.id_artist = 2;

Answer 2

您是否可以将WHERE条件添加到此查询中，并查看

SELECT  a.*, ai.*, av.* 
FROM    artist a 
JOIN    artist_pic ai on ai.id_artist = a.id  
JOIN    artist_video av on av.id_artist = a.id
WHERE   a.id = 2 

Answer 3

您必须使用UNION命令来合并两个查询：

SELECT a.*, 0 as is_video, ai.primarykey
     FROM artist a INNER JOIN artist_pic ai on ai.id_artist = a.id  
         where ai.id_artist = '" + idArtist + "' and ai.elemento = 'artist'
UNION
SELECT a.*, 1 as is_video, av.primarykey
     FROM artist a INNER JOIN artist_video av on av.id_artist = a.id  
         where av.id_artist = '" + idArtist + "' and av.elemento = 'artist'

请注意，只有当两个部分具有相同的列类型时，它才有效，因此您可能不能使用ai.*和av.*，而必须分别指定每个列。我还添加了一个"is_video“列来区分图片和视频(如果需要)。

如果添加artist_picture和artist_video的name和type列，则应该会得到类似以下内容：

   michael jackson   Img/5345.jpg     concert
   michael jackson   Img/3453.jpg     family
   michael jackson   video/5345.avi     concert
   michael jackson   video/2344.avi     family