Ahmed Ahmed - 5 months ago 28
SQL Question

Sql query to find Album records which have no Song in the Songs table

I am using a sqlite database in C# and need help with a a sql query, I do not know if what I am trying to get is possible in single query my current solution is very inefficient and poor which I have put it in the end but first my problem...

I have two tables Songs and Albums, the tables are shown below

I need to write a query to get all Album records which have no song in the songs table...

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 Hits


Need all records of Albums which have no Song in the Songs table,.

So in this case the records of Album I need are

Taylor Swift Beautiful Eyes
Shakira The remixes
Jackson Hits


My current solution is very inefficient and present below , I only need help with query not the C# code..

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

You can use SQL statement EXISTS to check if no songs exist for specific album:

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
)

This SQL Statement returns all records from table Album, that have no related records in table Songs.