interesting join issue 1

[bdog2020]

Hi there,
I've got 3 tables that I need to pull into a query. The tables look a bit like the below. What I need to do is get a query that returns just 3 rows. But, I'm getting more than that. I can do a DISTINCT in my query, and that gives me 3, but there are some text fields that I need to return, and it won't allow that. ItemID and Dealer are the key fields between the Detail1 and Detail2 table. thanks. BD

Item
========================
ItemID Status LastUpdated Category
B.01 Active <date> chair

Detail1
========================
ItemID Dealer ModelNumber Description
B.01 Staples S56 big long description
B.01 OfficMax O55 description
B.01 WB WB45

Detail2
=========================
ItemID Dealer Image Notes
B.01 Staples 1.jpg extended notes
B.01 OfficeMax 2.jpg more notes
B.01 WB 3.jpg extra notes

 

[IanWaterman]

Provided you join Detail 1 and 2 on ItemID and dealer you will only get 3 rows

select * from
Item i
inner join detail1 d1
on i.itemID = d1.itemID
inner join deatil2 d2
on d1.itemID = d2.itemID
and d1.dealer = d2.dealer

Ian

 

[bdog2020]

Ah, thanks Ian!