CODEWITHSUNDEEP
mysqlsql
cryptomath
cryptomath

Reputation: 141

JOIN returns empty set

I need to show the names and IDs of all artists who have not recorded titles but do have web addresses listed in my database.

This invovles two tables:

Artists
-------
ArtistID, ArtistName, City, Region, WebAddress

Titles
------
TitleID, ArtistID, Title, StudioID, Genre

My query is as follows:

 select ar.*
 from artists ar
 inner join titles t
 on ar.artistid = t.artistid
 where ar.webaddress != NULL;

which returns an empty set.

Upvotes: 0

Views: 959

Answers (3)

M.Ali
M.Ali

Reputation: 69524

SELECT ar.*
FROM Artists a INNER JOIN Titles T
ON A.ArtistID = T..ArtistID
WHERE a.WebAddress IS NOT NULL 
AND T.Title IS NULL

This will return records where there is a Webaddress but no title for a person

Upvotes: 1

pcreech
pcreech

Reputation: 334

In MySql, null is not a value, so where ar.webaddress != NULL; will not work. There is special syntax for checking nulls, is null and is not null for example. Also, the inner join will only give you artists that have titles. To get artists without titles, try outer join and check for null in the joined table

i.e.

 select ar.*
 from artists ar
 left join titles t
 on ar.artistid = t.artistid
 where ar.webaddress Is not NULL
 and t.ArtistID is null;

Upvotes: 4

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Note that null is not a value so you cant mention it with equal or not equal symbol: Try this:

select *
from artists
where ar.webaddress Is not NULL
and artistid not in(select distinct artistid in titles)

Upvotes: 3

Related Questions