Jump to content


Photo

left join select


  • Please log in to reply
8 replies to this topic

#1 akabugeyes

akabugeyes
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 21 February 2006 - 05:24 AM

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:

users
fields: userID, memName (and some other non relevant ones)

reviews
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
1 |akabugeyes|1 |blah |1
2 |admin |2 |blah2 |2


I've tried this:
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
but that doesn't work right. Any help would be appreciated. Thanks.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 February 2006 - 06:02 AM

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)
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 akabugeyes

akabugeyes
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 21 February 2006 - 03:48 PM

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:
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


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 February 2006 - 08:12 PM

(deleted)
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 21 February 2006 - 09:38 PM

[!--quoteo(post=347866:date=Feb 21 2006, 12:02 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 21 2006, 12:02 AM) View Post[/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

SELECT r.review, r.gameID, r.memID, u.memName, u.userID FROM reviews r LEFT JOIN users u ON r.memID=u.userID
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.

#6 akabugeyes

akabugeyes
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 21 February 2006 - 10:14 PM

Thank you wickning1. Indeed that method did work.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 February 2006 - 11:27 PM

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:

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

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 February 2006 - 04:22 AM

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).

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 February 2006 - 08:05 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users