Select only one row with your left join?

[rockinaway]

I have several left joins to my SQL query.. and one of the LEFT joins will return several rows. For that specific left join I want to only select the first row. How would I do that?

[toplay]

It's always best to post the query you have when posting on this forum and be more specific. Show sample data of what you have and what you'd like to see.

 

Choices: narrow where clause, sub query, group by, distinct, limit ... just depends on exactly what's needed.

 

 

 

 

[rockinaway]

SELECT c.name AS cname, b.*, t.title, m.name, m.gid, g.colour, u.id AS u_id
                                           FROM test_c AS c
                                           LEFT JOIN test_m AS m ON t.id = m.id
                                           LEFT JOIN test_g AS g ON m.gid = g.gid
	                           LEFT JOIN test_lu AS u ON u.id = b.id 
                                           ORDER BY c.p ASC, b.p ASC

 

The LEFT JOIN test_lu AS u ON u.id = b.id, returns several rows.

 

I just want the first row, when the times are put into DESC order. So I guess a WHERE and LIMIT need to be used.. but I am not sure how..

 

 

[toplay]

SELECT c.name AS cname, b.*, t.title, m.name, m.gid, g.colour, u.id AS u_id
                                           FROM test_c AS c
                                           LEFT JOIN test_m AS m ON t.id = m.id
                                           LEFT JOIN test_g AS g ON m.gid = g.gid
	                           LEFT JOIN test_lu AS u ON u.id = b.id 
                                           ORDER BY c.p ASC, b.p ASC

 

The LEFT JOIN test_lu AS u ON u.id = b.id, returns several rows.

 

I just want the first row, when the times are put into DESC order. So I guess a WHERE and LIMIT need to be used.. but I am not sure how..

 

 

Your query doesn't make any sense since I see an alias "b" that's not tied to any table. I just see alias "c", "m", "g", "u", but no "b".

 

Here I assume that you meant c.id and not b.id:

 

...

LEFT JOIN test_g AS g ON m.gid = g.gid

LEFT JOIN (SELECT id FROM test_lu WHERE id = c.id ORDER BY a_column DESC LIMIT 1) AS u ON u.id = c.id

ORDER BY ...

 

But since you didn't display any sample data and what you want to retrieve, it's hard to say. You might want to rethink the whole query. When I see too many left joins, something is usually a miss.

 

Good luck.

 

[rockinaway]

Thanks, I tried it but I get an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT id FROM test_lu WHERE id = c.id ORDER BY time DES' at line 7

 

SELECT c.name AS cname, c.id, t.title, m.name, m.gid, g.colour, u.id AS u_id
                                           FROM test_c AS c
                                           LEFT JOIN test_m AS m ON t.id = m.id
                                           LEFT JOIN test_g AS g ON m.gid = g.gid
	                           LEFT JOIN test_lu (SELECT id FROM test_lu WHERE id = c.id ORDER BY a_column DESC LIMIT 1) AS u ON u.id = c.id 
                                           ORDER BY c.p ASC, b.p ASC

 

That is what I am using :S

[rockinaway]

Fixed it

[rockinaway]

Actually it isn't fixed..

 

You see.. I have 4 main ids..

 

Each id has several pieces of info stored in the other table.

 

With my current query, I get all the pieces of info, making some ids have more than others. I just want to select ONE piece of information from the table for that id.

 

How would I do that?

[rockinaway]

Any help?

[rockinaway]

Bump..Anyone? Please..

[fenway]

Maybe you can explain what you want again... I'm not sure from the above what went wrong.

[rockinaway]

No worries.. fixed it