INNER JOIN on 3 fields?

[qwert231]

I'm an idiot, first off, so forgive my stupidity. I want to do an INNER JOIN query, here's the thing...

Table1:
ItemGroup, Item1, Item2, Item3

Table2:
Item, ItemPrice

For each Item in an Item Group (Item1, Item2...) in Table1 I would like to have the price from Table2.

What's a good way to do an INNER JOIN on this? Can I do it? I'm trying it now... any ideas would be okey-day..

 

[Ankor]

SQL Server has a great function called COALESCE - it returns the first non-null value from its arguments. I am not quite sure that I understood how your tables look like, but I think this function can help.

 

[bperry]

Would you like to try this?

Select
ig.ItemGroup,
ig.Item1,
p1.ItemPrice,
ig.Item2,
p2.ItemPrice,
ig.Item3,
p3.ItemPrice
From ItemGroup ig
INNER JOIN ItemPrice p1
ON ig.Item1 = p1.Item
INNER JOIN ItemPrice p2
ON ig.Item2 = p2.Item
INNER JOIN ItemPrice p3
ON ig.Item3 = p3.Item
WHERE any where criteria you may want

-----------------------
bperry

 

[bperry]

My apologies, I assumed you were probably using Oracle or maybe SQL Server. If you are using Access, then my previous suggestion may not work.

 

[qwert231]

Yea, I should learn to specify better... tho I think I figured a different work around. I'll drop it in here tomorrow, as I am home now. Thanks.

It is Access.

 

[harmmeijer]

If it is not a problem for you to make your database in the 3rd normal form use this:

tblItemGroups
itemGroupID groupDesc

tblItemsGroups
itemGroupID itemID

tblItems
itemID price itemDesc

SELECT groupDesc, itemDesc, price FROM
(tblItemGroups AS gr LEFT JOIN tblitemsGroups AS ig ON ig.itemGroupID = gr.itemGroupID)
LEFT JOIN tblItems AS i
ON i.itemID = ig.itemID;