Jump to content

Archived

This topic is now archived and is closed to further replies.

akabugeyes

left join select

Recommended Posts

I am having a problem with a simple join statement that I want to connect two tables together with.

My two tables look something like this:

[i]users[/i]
fields: userID, memName (and some other non relevant ones)

[i]reviews[/i]
fields: gameID, memID, review

What I want to do is join the tables so I can match up the memberID of each one to produce the member name.

So for example say the users table looked like this:
userID|memName
1 | akabugeyes
2 | admin

and the reviews table looked like this:
memID|review|gameID
1 |blah | 1
2 |blah2 | 2

I would want them to join like this:
userID|memName |memID|review|gameID
[b]1[/b] |akabugeyes|[b]1[/b] |blah |1
[b]2[/b] |admin |[b]2[/b] |blah2 |2


I've tried this:[code]SELECT a.review, a.gameID, a.userID, b.memName, b.memID FROM reviews as a, users as b LEFT JOIN users  ON a.userID = b.memID[/code]
but that doesn't work right. Any help would be appreciated. Thanks.

Share this post


Link to post
Share on other sites
Change the word LEFT to INNER, and you'll have what you desired -- LEFT matches those rows which are found in the "left" table, and not the "right". You want to have a match in both.

(see comments later on in this thread)

Share this post


Link to post
Share on other sites
Thank you, that did it. :)

For some reason I also had to put DISTINCT after SELECT or it would select the same rows three times each.

Like this:
[code]SELECT DISTINCT a.review, a.gameID, a.userID, b.memName, b.memID FROM reviews as a, users as b INNER JOIN reviews ON a.userID = b.memID[/code]

Share this post


Link to post
Share on other sites
[!--quoteo(post=347866:date=Feb 21 2006, 12:02 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 21 2006, 12:02 AM) [snapback]347866[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Change the word LEFT to INNER, and you'll have what you desired -- LEFT matches those rows which are found in the "left" table, and not the "right". You want to have a match in both.
[/quote]
Actually that's not quite correct, LEFT JOIN is just like an inner join except that it prints each row from the left table AT LEAST ONCE.

For instance, given these tables:
Reviews
memID|review|gameID
1|blah|2
3|blah2|1

Users
userID|memName
1|admin
2|akabugeyes

[code]SELECT r.review, r.gameID, r.memID, u.memName, u.userID FROM reviews r LEFT JOIN users u ON r.memID=u.userID[/code] would return:
blah|2|1|admin|1
blah2|1|3|NULL|NULL

It's a good way to guarantee that you're seeing all the reviews even with referential integrity problems (or when the info in the right table is optional).

BTW akabugeyes, that query I wrote will work for you. It will also work if you replace LEFT JOIN with INNER JOIN. All the other queries in this thread are incorrect or inconsistent.

Share this post


Link to post
Share on other sites
Wow... I didn't even notice that you had a 3rd table in the initial post both times that I replied. Good catch.

IMHO, it's simpler to use a multi-table select even though everyone loves JOINs:

[code]SELECT r.review, r.gameID, r.memID, u.memName, u.userID FROM reviews AS r, users AS u WHERE r.memID=u.userID[/code]

Furthermore, if you're not using InnoDB tables, and referential integrity isn't enforced, it's always good to "know" there's a problem, rather than "hide" it with a LEFT JOIN.

Share this post


Link to post
Share on other sites
The comma is just another way to write an INNER JOIN.

I find that LEFT JOIN helps me detect referential integrity problems in a lot of cases, because I get rows with obviously bad data, instead of the rows just disappearing into the abyss. But yeah it's not a good long term solution to simply cope with bad integrity.

I just use LEFT JOIN any time I'm writing a query that I expect to return all the applicable rows from the left table (subject to the WHERE clause).

Share this post


Link to post
Share on other sites
Agreed -- it's just that I find that many ppl have a much easier time with the comma syntax than the JOIN syntax. But the parser / optimizer doesn't know the difference.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.