July 8, 2005 at 9:03 am
I have two table TableA(Master table) and TableB(Detail) table. The key field between both tables is ID. Here are the data in both table>
TableA TableB
Id Product Id ItemNo
1 New 1 100
1 200
2 Old 2 300
3 Archive
I need query which can pull data like this:
ID Product ItemNo
1 New Either one 100 or 200
2 Old 300
3 Archive
How can i pull only one record (either one) from detail table (like ID=1) and also pull record from master which does not have any detail record (like ID=3)
July 8, 2005 at 9:11 am
Is this what you need?
Select P.Id, P.Product, min(D.ItemNo) as ItemNo from dbo.Products P left outer join dbo.Details D on P.id = D.id
group by P.Id, P.Product
order by P.Id, P.Product
July 8, 2005 at 1:18 pm
or this ?!?!
select P.Id, P.Product, D.ItemNo
from Product P
left outer join
(select Id, min(ItemNo) as ItemNo
from Details
group by Id) D
on P.id = D.id
order by P.Id, P.Product
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply