Join Table

[hc_loke]

All,
I have two tables, A and B.
Table A has names, address, phone.
Table B has names and image filename.
Table A and B can be linked by names.
I would like to show all the names in Table A and link to Table B such that it will show a column image filename if there is a image filename. If the name cannot be found in the Table B display a NULL on the column.
(Note : there may be two same names in the Table B, so how can i show only the first image filename).

Kindly advise.
thanks.

[skhanal]

select a.name, a.address, a.phone, b.image
from tableA as a
left join tableB as b
on a.name = b.name

This will give null for all names not in tableb. If you have duplicate names in b then it will show two records

[hc_loke]

hello Skhanal,
thanks for your reply.

If i need to only display just one of the record with the imagename when there is a duplicate, how should I choose the record.

Kindly advise. Thanks.

[skhanal]

If you have duplicate in B, you need to define the criteria to select only one. What are other columns in B?

[hc_loke]

hello skhanal,
table b has two columns name and imagfilename.

rgds

[skhanal]

Is imagefilename different or same for the name?

If it is same then


select a.name, a.address, a.phone, b.image
from tableA as a
left join
(select distinct name, image
from tableB) as b
on a.name = b.name

If imagefilename is different, then you have to use one of them.

select a.name, a.address, a.phone, b.image
from tableA as a
left join
(select name, max(image) as imagefile
from tableB
group by name) as b
on a.name = b.name