Complex join?

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

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.

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.

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?