...

View Full Version : Left Join not working correctly



bubbles19518
06-10-2009, 11:34 PM
Here is my table structure:
Table: User
Fields: UserId,FirstName,LastName,FriendCount

Table: Network_User
Fields: UserId,NetworkId

Table: Network
Fields: NetworkId, Network

Table: Keyword_User
Fields: UserId,KeywordId

Table: Keyword
Fields: KeywordId, Keyword

This query works correctly:

SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
LEFT JOIN `Network_User` nu ON nu.UserId = u.UserId
LEFT JOIN `Network` n ON nu.NetworkId = n.NetworkId
WHERE n.Network = 'Purdue' ORDER BY u.FriendCount DESC
It returns 2 rows.

This one does not:

SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
LEFT JOIN `Keyword_User` ku ON ku.UserId = u.UserId
LEFT JOIN `Keyword` k ON ku.KeywordId = k.KeywordId
WHERE k.Keyword = 'weeds' ORDER BY u.FriendCount DESC
It returns an empty set, where it should return 2 rows like the other one.

I know the correct data is in the tables for it to return.

I noticed that

SELECT * FROM `Keyword` WHERE `Keyword` = 'weeds'
Also fails.

Any idea why?

Old Pedant
06-11-2009, 12:57 AM
Humor me.

Try this query:


SELECT * FROM `Keyword` WHERE `Keyword` LIKE '%weed%'

I'm trying to see if perhaps you don't *REALLY* have any records with keyword "weed". As in, maybe there is a space in the value???

Heck, for that matter, try


SELECT * FROM `Keyword` WHERE `Keyword` LIKE '%ee%'

Or most anything that is likely to match *SOME* records.

bubbles19518
06-11-2009, 03:26 AM
No I've looked at the rows in phpMyAdmin. There are two rows in Keyword_User with the user ids and the keyword id of 'weeds' which is in the Keyword table.

Old Pedant
06-11-2009, 05:23 AM
Oh, stupid me!

This is one of my most popular "rants"!!!

When you use a WHERE clause to limit records from a *DEPENDENT* table in an outer join (that is, the right-side table in a LEFT join...the left side is the independent table), you just converted that OUTER join to an INNER join!!!!!!!!!

Look here to see why:
http://www.codingforums.com/showthread.php?p=818192#post818192

SO...actually, BOTH your queries are wrong. I just got misled when you said the one worked. You just got lucky in the first case.

Try these:


SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
LEFT JOIN `Network_User` nu ON nu.UserId = u.UserId
LEFT JOIN `Network` n ON ( nu.NetworkId = n.NetworkId AND n.Network = 'Purdue' )
ORDER BY u.FriendCount DESC

SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
LEFT JOIN `Keyword_User` ku ON ku.UserId = u.UserId
LEFT JOIN `Keyword` k ON ( ku.KeywordId = k.KeywordId AND k.Keyword = 'weeds' )
ORDER BY u.FriendCount DESC


SORRY!

(The parens in the ON condition aren't needed in MySQL but are in some DBs. But I like to put them in, regardless, because it clarifies the intent of the join.)

bubbles19518
06-11-2009, 06:09 AM
That Keyword query spits out 115, the same as the number of rows in the Keyword table. Sorry for my newbness, I'm horrible with joins.

I'm trying to select ONLY the users who have the 'weeds' keyword linked to their profile in the Keyword_User table.

Old Pedant
06-11-2009, 06:21 AM
Then you do *NOT* want a LEFT JOIN.

The definition of a LEFT JOIN is "get *ALL* records from the table on the left side of the JOIN and then get matching records from the right side table *IF THEY EXIST*...but even if there are not matching right side records, still get the all the left side records."

If you want to limit the records in the left side table, use INNER JOINs instead.

However....

Given that your misuse of the WHERE clause should have changed your LEFT JOIN *into* an INNER JOIN, I'm not sure what is going to happen.

But give it a shot:


SELECT u.UserId,u.FirstName,u.LastName,u.FriendCount FROM `User` u
INNER JOIN `Keyword_User` ku ON ku.UserId = u.UserId
INNER JOIN `Keyword` k ON ( ku.KeywordId = k.KeywordId AND k.Keyword = 'weeds' )
ORDER BY u.FriendCount DESC

You know, I don't think this is going to possibly work.

You said that just doing


SELECT * FROM `Keyword` WHERE `Keyword` = 'weeds'

returns no records, right? So you have a problem in that table and I have no idea why.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum