[RESOLVED] need a second join... maybe?

krotkruton

I have a query that takes a friends list (a table of pairs of friends where each pair is listed twice, so a pair of friends A and B show up as A,B and B,A) and gets all the friends of a certain user, then gets the information for each of the friends from my userprofiles table. This all works great with the following query:

$query_friends_total = "SELECT up.email, up.first_name, up.last_name, up.id
	FROM userprofiles up 
	LEFT JOIN friendslist f ON f.user = $userid 
	WHERE up.id = f.friend";

The problem is that I also need to get a variable from one other table. I need to get the image_id from my pictureprofile table where (with pictureprofile pp) pp.user_id = f.friend and pp.thumbnail != 0.
I tried to do it without an extra left join but failed. Does anyone know how to do this?

Roger_Ramjet

Your main problem here is that you have not written your first join correctly: you have the JOIN CONDITION in the WHERE clause and the WHERE CONDITION in the JOIN clause. I'm surprised that it works at all, but then mysql is very forgiving. Of course as soon as you try to extend the query it does fail. So the first thing to do is to correct the original query - then you will be able to join to the 3rd table without any trouble.

 $query_friends_total = "SELECT up.email, up.first_name, up.last_name, up.id
    FROM userprofiles up
    LEFT JOIN friendslist f ON up.id = f.friend 
    WHERE f.user = $userid";

Can you see the difference: you join the 2 tables on the columns that should match and then restrict the results to a specific user.

Now have a go at adding the second join to the 3rd table to get the image data.

Lars_Berg

The first query is correct, syntacically and semantically, so it would be more suprising if it was rejected by any DBMS.

What is dubious though, is the use of a left join in both queries. Since there are restrictions on the right-hand table in the where clause it will be the same as an inner join.

Roger_Ramjet

Lars Berg wrote:
The first query is correct, syntacically and semantically, so it would be more suprising if it was rejected by any DBMS.

No it is not Lars, not correct at all. The ON clause in krotkruton's query does NOT reference BOTH tables and is not therefore a join condition. The fact that the query still works is because mysql is so lax in it's syntax that the where clause is acceptable; actually a backwards compatibility thing since mysql did not support join conditions for quite a while. "Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11."

It would not work in Access for sure, and probably not in MS SQL or Oracle.

Lars Berg wrote:
What is dubious though, is the use of a left join in both queries. Since there are restrictions on the right-hand table in the where clause it will be the same as an inner join.

You are right in that the where condition should be applied to the userprofiles table: I just copy/pasted it is my excuse. I assumed the left join was for users who had no friends.

Lars_Berg

The query is correct. The on clause is a search condition, i.e. something that evaluates to a boolean value. It does not need to reference any tables.

krotkruton

Roger Ramjet wrote:
Can you see the difference: you join the 2 tables on the columns that should match and then restrict the results to a specific user.

Yes, and the explanation helped a lot. I've never really understood how the joins work, and still don't entirely, but now I have a better grasp on it.

Sadly though, after thinking about my problem for a while, I'm not sure if I need the query anymore since I don't want to pull a lot of image information if only a few images will be displayed on a page (since a person might have hundreds of friends but will only view 10 or 20 at a time). Regardless, the advice has been very helpful. Thanks again.