Sql查询以查找歌曲表中没有歌曲的相册记录

Question

我正在使用C#中的sqlite数据库，需要一个sql查询的帮助，我不知道我想要得到的结果是否可以在一个查询中实现--我目前的解决方案效率很低，而且很差--我已经把它放在最后，但首先我的问题是……

我有两个表，歌曲和专辑，表如下所示

我需要写一个查询，以获得所有专辑记录，其中没有歌曲在歌曲表.

    Songs

    Artist           Album         Filename        
    Taylor Swift     Red           track1.mp3      
    Taylor Swift     Red           track2.mp3      
    Taylor Swift     Fearless      track1.mp3      
    Taylor Swift     Fearless      track2.mp3    
    Shakira          GrandesExitos track1.mp3


    Albums

    Artist           Album 
    Taylor Swift     Red    
    Taylor Swift     Fearless  
    Taylor Swift     Beautiful Eyes  
    Shakira          The remixes
    Jackson          Fearless
    Taylor Swift     The remixes

需要所有唱片的专辑，没有歌曲在歌曲表，。

    So in this case the records of Album I need are

    Taylor Swift     Beautiful Eyes  
    Shakira          The remixes
    Jackson          Fearless
    Taylor Swift     The remixes

我目前的解决方案效率很低，目前在下面，我只需要帮助查询，而不是C#代码。

    dt = db.GetDataTable(String.Format("select distinct artist, album from files");

    foreach (DataRow r in dt.Rows)
    {
       string artist = (string)r[0];
       string album = (string)r[1];
       //I have create a status column in the Albums table to solve this problem
       query = string.Format("update Albums set status = {0} where artist = '{1}' and album = '{2}'", 1, artist, album);

       int nModified = db.ExecuteNonQuery(query);
     }
     // Then I get all records from Albums whose status is not 1
      dt = db.GetDataTable(select * from Albums where status = 0");

     }

Answer 1

可以使用SQL语句EXISTS检查特定相册是否不存在歌曲：

SELECT alb.Artist, alb.Album
FROM Albums alb
WHERE NOT EXISTS (
    SELECT 1 
    FROM Songs s 
    WHERE s.Artist = alb.Artist AND s.Album = alb.Album
)

此SQL语句返回表Album中的所有记录，这些记录在表Songs中没有相关记录。

Answer 2

你可以试试这个

Select * from albums where album not in(select album from songs)

Answer 3

SELECT artist,album FROM Albums WHERE album NOT IN(SELECT DISTINCT Album FROM songs)