[SOLVED] LEFT JOIN limit

[oldschool]

table1

 

id

headline

 

table2

 

id

parent

image

 

 

Currently I've got

 

SELECT a.headline, b.image FROM table1 a LEFT JOIN table2 b ON b.parent=a.id 

 

but what I want to do is list all the headlines from table 1 and display one image from table2 for each headline...

 

Does anybody know where I'm going wrong here?

[Illusion]

why do u need a join if  u have a id corresponding to every parent.

 

SELECT a.headline, b.image FROM table1 a , table2 b where b.parent=a.id

 

if u don't  have corresponding parent in table2 for each id then

 

Select headline from table1 LEFT JOIN (select image from table2) ON b.parent=a.id

[Wildbug]

 

Does anybody know where I'm going wrong here?

 

The query looks fine.  What are or aren't you getting that is unexpected?

 

 

 

Select headline from table1 LEFT JOIN (select image from table2) ON b.parent=a.id

 

A) Why would you use a subquery when you can use a JOIN?  b)  Why would you do both?  c) You need to select id in the subquery if you want to reference it in the outer query.  D) You need to alias the table and the subquery before using those aliases in the ON clause.

[Illusion]

Yes , I forgot to use alias names 

 

Select headline from table1 as a LEFT JOIN (select image from table2 as b)  ON b.parent=a.id

 

Why would you do both?

I just given a alternative to what oldschool has written. Is there any thing wrong with the query , I mean will it not give expected results?. I know it make a difference performence prospective.

[Wildbug]

Why would you do both?

I just given a alternative to what oldschool has written. Is there any thing wrong with the query , I mean will it not give expected results?. I know it make a difference performence prospective.

 

Yes, you need to select the id in the subquery if you want to reference it outside in the ON clause.  (SELECT id,image FROM table2) AS b....etc.  But since this is an easy LEFT JOIN, there's not really a need for subqueries, which, yes, tend to be less efficient than JOINs.

[Illusion]

yes, I have missed id column there.........you know I have bad habit , I don't review my queries.

anyway thanks buddy!