left join select

[akabugeyes]

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.

[fenway]

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)

[akabugeyes]

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]

[fenway]

(deleted)

[wickning1]

[!--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.

[akabugeyes]

Thank you wickning1. Indeed that method did work.

[fenway]

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.

[wickning1]

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

[fenway]

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.