problem with join

[jannat]

Hi all,

I have to join three tables, one of the table has id and other fields, the other table has description but for one id, there are more than one description, I need to only pick up one description and other fields from other tables, how would I be able to do it?

please help.

[guytz72]

Hi,

You need to join on the ID's and also the description:

eg:

Select tbl1.ID, tbl1.ColA, tbl2.ID, tbl2.Desc, tbl2.Data1
From tbl1
inner join tbl2 on tbl1.ID = tbl2.ID and tbl2.Desc = 'Your Description'

Hope this helps.

Guytz

[jannat]

It won't work in my case because the description is not always the same, I just need to pick any of them randomly...

[nosepicker]

SELECT tbl1.id, tbl2a.MaxDesc
FROM tbl1 JOIN
(SELECT id, MAX(description) AS MaxDesc FROM tbl2 GROUP BY id) AS tbl2a
ON tbl1.id = tbl2a.id

(you don't have to use MAX - can use MIN if you want - if this doesn't produce unique records, then you need to group by additional columns)

[jannat]

it worked, thanks a lot!!!