Jump to content

left join select


akabugeyes

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.
Link to comment
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]
Link to comment
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.
Link to comment
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.
Link to comment
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).
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.