Criteria in left join

[cog]

Hi all, hope you can help me.... stuck with a not to complicated query... but nonetheless the logic escapes me... So helping out with this query as well as pointing me to an easy to understan ressource would be sppreciated

I have two tables.

CREATE TABLE `profiles` (
`pid` int(11) NOT NULL auto_increment,
`pactivated` tinyint(1) NOT NULL default '0',
`pcreated` date NOT NULL,
PRIMARY KEY (`pid`),
);

CREATE TABLE `recommendations` (
`rpid` int(11) NOT NULL,
`rverified` tinyint(1) NOT NULL,
KEY `rpid` (`rpid`)
);

The two tables arre linked through profiles.pid and recommendations.rpid
I want to list profiles, and their number of recommendations.. That is no problem, but limiting them to activated and verifed eludes me.

This work, but doesn't check whether the counted rows are verified (=1)

SELECT
pid, COUNT(rpid)
FROM profiles LEFT JOIN recommendations as r
ON pid=rpid
WHERE pactivated>0
GROUP BY rpid
ORDER BY pcreated

I want i to count rows in the recommendations table where rverified=1... but I cant get it to work. Tried HAVING, AND on the WHERE part... Anyone?? Thx

Sorry 'bout the length of the post... just summing up
I want to count all recomendations where rverified is true (=1) matching all activated profiles (pactivated=1)

[guelphdad]

be careful when using LEFT JOINs. Because the condition you want is on the right hand table (rverified being in that table), that particular condition belongs in an AND clause after the table join. If you include it as part of the where clause you effectively change your LEFT JOIN to an INNER JOIN.

Let's look at what your query needs to be and then I'll give you the scenario as to what happens that turns a query into an inner join.


SELECT
pid, COUNT(rpid)
FROM profiles LEFT JOIN recommendations as r
ON pid=rpid
AND rverified = 1
WHERE pactivated>0
GROUP BY rpid
ORDER BY pcreated


Now the example.

you have profiles and recommendations. when you use a left join to me it says you want to show the profiles even if they don't have a recommendation yet. If you didn't want that you would use an INNER JOIN right? That gives only matching rows.

So let's use a sample scenario and change your tables a bit so it is easier to see and others to follow. Lets give a pid and a name.

pid, name
1 Bob
2 Tom
3 Joe
4 Larry

and recommendations the rpid will be the profile of the person from our first table and the rverified will be the id of the person who verifies that person as being a good chess player for instance

rpid, rverified
1, 2
1, 4
2, 3
4, 1
4, 2
4, 3

okay so looking at that by eye we can see that Bob has played both Tom and Larry and recommends them as good opponents. Tom thinks Joe is a good opponent and everyone thinks Larry is a good opponent.

But notice while both Larry and Tom have played Joe there is no row showing Joe as a good opponent, maybe he is lousy, or maybe Larry and Tom have been truant in filling in a recommendation for Joe.

So now let us use an INNER JOIN and get the profile id and the name of the player as well as the id of the person who recommended them. (we could get the name of that person but that involves another table join and would complicate the illustration, though not change it).


mysql> SELECT
-> pid, name, rverified
-> FROM
-> profiles
-> INNER JOIN
-> recommendations
-> ON pid=rpid;
+------+-------+-----------+
| pid | name | rverified |
+------+-------+-----------+
| 1 | Bob | 2 |
| 1 | Bob | 4 |
| 2 | Tom | 3 |
| 4 | Larry | 1 |
| 4 | Larry | 2 |
| 4 | Larry | 3 |
+------+-------+-----------+
6 rows in set (0.00 sec)


So we see that Joe isn't there at all, but you used a LEFT JOIN because you want to see that Joe has a profile even if he hasn't been verified


mysql> SELECT
-> pid, name, rverified
-> FROM
-> profiles
-> LEFT JOIN
-> recommendations
-> ON pid=rpid
-> ;
+------+-------+-----------+
| pid | name | rverified |
+------+-------+-----------+
| 1 | Bob | 2 |
| 1 | Bob | 4 |
| 2 | Tom | 3 |
| 3 | Joe | NULL |
| 4 | Larry | 1 |
| 4 | Larry | 2 |
| 4 | Larry | 3 |
+------+-------+-----------+
7 rows in set (0.00 sec)


So this is setting up for the problem of turning a LEFT JOIN into an INNER JOIN. What did I mean by that you ask.

Let's change our scenario a bit. Suppose we only want to see good chess players as recommended by Joe (rpid 3)


mysql> SELECT
-> pid, name, rverified
-> FROM
-> profiles
-> LEFT JOIN
-> recommendations
-> ON pid=rpid
-> WHERE rverified = 3;
+------+-------+-----------+
| pid | name | rverified |
+------+-------+-----------+
| 2 | Tom | 3 |
| 4 | Larry | 3 |
+------+-------+-----------+
2 rows in set (0.00 sec)


but that isn't exactly what you were looking for. You wanted to see all profiles, so you want those profiles to show up even if Joe didn't recommend them, so you need to move the condition from the RIGHT HAND table out of the WHERE clause and into an AND clause like this:


mysql> SELECT
-> pid, name, rverified
-> FROM
-> profiles
-> LEFT JOIN
-> recommendations
-> ON pid=rpid
-> AND rverified = 3;
+------+-------+-----------+
| pid | name | rverified |
+------+-------+-----------+
| 1 | Bob | NULL |
| 2 | Tom | 3 |
| 3 | Joe | NULL |
| 4 | Larry | 3 |
+------+-------+-----------+
4 rows in set (0.00 sec)


Now my example is trivial, you see though that you need to be careful when setting a condition in the right hand table in a LEFT JOIN.

By the way the same would be true in setting a condition on the left table when using a RIGHT JOIN.

I prefer not to use RIGHT JOINs only because I read from left to right and it makes sense for me to think of my tables in that way. I suppose an Arabic reader would be more comfortable using RIGHT JOINs for the same reason. The caveat of course is not to mix the two.

[guelphdad]

Oh, one last thing I noted, in your original select you selected pid but then grouped on rpid, change your group by to pid. It is easy to make errors when grouping. This is only for clarity purposes, but will keep you in good habits.

[cog]

Wow

Thats a lengthy reply.... And right to the point.... Thanks for your time, looks like it's smack on....

Never new you could use JOIN ON ...... AND

Superb answer, you'll surely get a recommendation once its up and running ;-)