MySQL left join doesnt give me what i expect

Question

I'd like some help with an left join statement thats not doing what i, probably incorrectly, think it should do.

there are two tables:

cd:

CREATE TABLE `cd` ( `itemID` int(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `artist` text NOT NULL, `genre` text NOT NULL, `tracks` int(11) NOT NULL, PRIMARY KEY (`itemID`) )

loans

CREATE TABLE `loans` ( `itemID` int(11) NOT NULL, `itemType` varchar(20) NOT NULL, `userID` int(11) NOT NULL, `dueDate` date NOT NULL, PRIMARY KEY (`itemID`,`itemType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and i want to select all cd's thats not in loans using a left join and then an where dueDate is null

select t.itemID, t.artist as first, t. title as second, (select AVG(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `rating avarage`, (select COUNT(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `number of ratings` from cd t left join loans l on t.itemID = l.itemID where l.itemType = 'cd' and l.dueDate is null;

this one however returns an empty table even though there are plenty rows in cd with itemIDs thats not in loans

now i was under the understanding that the left join should preserv the righthandside and fill the columns from the lefthandside with null values but this does not seem to be the case, can anbyone enlighten me?

change 'where' to 'and' and change 'and' to 'where'

Strawberry
– Strawberry

2014-05-29 15:43:36 +00:00
Commented May 29, 2014 at 15:43 — Strawberry
– Strawberry, Commented May 29, 2014 at 15:43

Pred · Accepted Answer · 2014-05-29 15:51:06Z

Your WHERE condition causes the error. The L.ItemType = 'cd' will always return false if the L.DueDate IS NULL is true. (All of your fields are NOT NULL, so the DueDate can only be NULL if there is no matching records, but in this case the ItemType field will be NULL too).

Another point is that your query is semantically incorrect. You are trying to get the record from the cd table where the loans table do not contains any rows with dueDates. The second table acts as a condition, so it should go to the WHERE conditions.

Consider to use the EXISTS statement to achieve your goal:

SELECT t.itemID, t.artist as first, t. title as second, (select AVG(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `rating avarage`, (select COUNT(rating) from ac9039.ratings where itemType = 'cd' and itemId = t.itemID) as `number of ratings` FROM cd t WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE t.itemID = l.itemID AND L.itemType = 'cd')

Based on your data model you have to add another condition to the subquery to filter out those records which are out-of-date now (dueDate is earlier than the current time)

This is the case, when you do not delete outdated loan records.

NOT EXISTS (SELECT 1 FROM loans l WHERE t.itemID = l.itemID AND AND L.itemType = 'cd' l.dueDate > NOW())

The query in the question is semantically incorrect, this is why I suggested the EXISTS aproach. OK, and now I see what do you mean :)
@Strawberry I've just updated my answere to be a bit more precise.

Collectives™ on Stack Overflow

MySQL left join doesnt give me what i expect

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related