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

[nikolayivanovbg]

Hello guelphdad,

It was a very good explanation of LEFT JOIN becoming INNER JOIN.

I used to know this a long time ago, but now i faced another problem.

In this select everything works fine:
SELECT p.id, p.required, p.show_like, d.danni_instance_id, d.data
FROM form_poleta p
LEFT JOIN form_danni d on d.pole_id = p.id AND d.danni_instance_id = 84
WHERE p.hidden = 0 AND p.forma_id = 1
ORDER BY p.orderno

But if i want to select more than one danni_instance_id and put IN in the left join - it becomes INNER JOIN:

SELECT p.id, p.required, p.show_like, d.danni_instance_id, d.data
FROM form_poleta p
LEFT JOIN form_danni d on d.pole_id = p.id AND d.danni_instance_id IN (84, 85)
WHERE p.hidden = 0 AND p.forma_id = 1
ORDER BY p.orderno

Any help is appreciated!

P.S. It didn't help me when i changed
AND d.danni_instance_id IN (84, 85)
to
AND (d.danni_instance_id =84 OR d.danni_instance_id = 85)
- the final result is the same - the LEFT JOIN becomes INNER JOIN :mad:

[guelphdad]

What rows did you expect to get back from your table and what rows are you getting back? can we see some sample data?

you aren't running into problems with how you have it since your dependent clause from the right hand table is in the join and not the where clause.

[nikolayivanovbg]

In left table i have 15 rows.
In the right one for the first ID, for example 84 i have only 8 matching rows.
For the second ID - 175 I have all 15 rows matched.

If I execute the query with this AND d.danni_instance_id = 84 in the LEFT JOIN I get similar result:

id required show_like danni_instance_id data
1 1 textbox 84 Клиент Номер 4
2 0 textbox 84 Фирмата ООД 333
3 1 textbox 84 nikolai.ivanov2 нов клон@alsys.ro
4 1 textarea 84 Адрес ред 1
5 0 textbox 84 02/ 123 456 789 02 нов клон
6 1 checkbox 84 6,7,8
7 1 radio 84 14
8 0 radio 84
9 0 textbox NULL NULL
43 0 date NULL NULL
44 0 combo NULL NULL
45 0 listbox NULL NULL
48 1 mail NULL NULL
47 1 integer NULL NULL
46 1 url NULL NULL

If I execute the query with this AND d.danni_instance_id IN (84, 175) in the LEFT JOIN I get this (LEFT JOIN becomes INNER):

id required show_like danni_instance_id data
1 1 textbox 84 Клиент Номер 4
2 0 textbox 84 Фирмата ООД 333
3 1 textbox 84 nikolai.ivanov2 нов клон@alsys.ro
4 1 textarea 84 Адрес ред 1
5 0 textbox 84 02/ 123 456 789 02 нов клон
6 1 checkbox 84 6,7,8
7 1 radio 84 14
8 0 radio 84
1 1 textbox 175 5-ти запис in Deutsch 2
2 0 textbox 175 5-ти запис
3 1 textbox 175
4 1 textarea 175 5-ти запис
5 0 textbox 175 5-ти запис
6 1 checkbox 175 6,8,13
7 1 radio 175 15
8 0 radio 175 7
9 0 textbox 175
43 0 date 175 -80126661
44 0 combo 175 13
45 0 listbox 175 13
46 1 url 175 www.google.com
47 1 integer 175 11
48 1 mail 175 niliev@bul....com

Result is the same, if I substitute AND d.danni_instance_id IN (84, 175)
with this:
AND (d.danni_instance_id = 84 OR d.danni_instance_id = 175)

You can see that records with NULL values (from the LEFT JOIN) are missing - thats why I think it becomes an INNER JOIN now.

Thanks for the fast reply on this old thread! :)

P.S. Don't look at the data itself, most of it is in Cyrillic (bulgarian language)

[nikolayivanovbg]

All written from me before remains true.
Maybe this is bug or in LEFT JOIN clause you can put only simple AND. Every more complicated condition transform LEFT JOIN to INNER.

A solved it for me with 2 different queries and one cycle looping almost parallel on both record-sets.