Complex join?

[idesdema]

I have 3 tables. Comments, profiles, and pictures. First I pull out 3 rows from the Comments table. After I get those comments, I want to pull the profile from profiles that made each comment, and then lastly pull the pic from pictures that is attached to profiles. I can't seem to get it right. I want to display the profile pic with some profile info along with the comment.

Right now I do this and it displays 3 pics with one profile summary and one comment, then 2 pics, then 1.

Rough code:

<!--- Get comments --->
<cfquery name="comments">
SELECT * FROM comments WHERE comment_id = '2'
</cfquery>

<cfoutput query="comments">
<!--- Join comments --->
<cfquery name="comment_profiles">
SELECT p.*,d.*
FROM profile p, pictures d
WHERE p.id = d.id AND p.id = '#comments.comment_id#'
GROUP BY p.id
</cfquery>

When I display this it acts strangely... My code must be a mess!

[Dan_Bracuk]

For your syntax, instead of

where somefield = somelist
use
where somefield in (somelist)

Plus, you need to use the valuelist function.

But why not join all three tables in one query?

[idesdema]

I looked up "3 table join" and got some info from mysql's site. They recommended attacking it in two phases which I have done. Phase 1 - Join table 1 and 2 = done. No problems. Phase 2 - Add table 3 to first join... struggling. :)

I'm going to give it a try for a bit and if I can't come up with it I'll come back here.

[idesdema]

Ok... I am getting frustrated.

I can't get this to work. I'm trying to join three tables, the last join is one to many and I want one row, by id desc to be joined. Here's what I've got so far and it's not working.

SELECT c.*,m.*,(SELECT * FROM pictures LIMIT 1 ORDER BY id ASC) as p
FROM comments c
LEFT JOIN profile m ON c.comment_id = m.id
WHERE c.p_id = m.id AND c.p_id = p.p_id
ORDER BY c.id DESC;

The error is when I try to order the pictures select statement.

[Newsgroup_User]

idesdema wrote:
> I can't get this to work. I'm trying to join three tables

Your code is not joining three tables it is joining two tables combined
with a sub-query select. That seems rather convoluted for what you
describe as your requirements.

> SELECT c.*,m.*,(SELECT * FROM pictures LIMIT 1 ORDER BY id ASC) as p
> FROM comments c
> LEFT JOIN profile m ON c.comment_id = m.id
> WHERE c.p_id = m.id AND c.p_id = p.p_id
> ORDER BY c.id DESC;

I can not determine your database design from this query, it is quite
muddled, you have a sub-query select statement, you apparently have FROM
clause Join syntax and WHERE clause ANSI 92 join syntax. It is quite
confusing. Can you provide a simple description of the comment, profile
and picture tables and what the key and foreign key fields that related
these tables are.

I would normally approach this problem something like this.

SELECT aField, bField, cField

FROM comments c
LEFT JOIN profile m ON c.comment_id = m.id
INNER JOIN pictures p ON m.p_id = p.p_id