[buterfly0707]

hi all..
i have 3 tables called itemMaster,ItemWh,whWiseItem(table names i took just for example). here i want to retrieve data from all the 3 tables.

table details
itemMaster having details of Item means Code,Description,UOM etc.
ItemWh-items fro perticular store.
whWiseItem-also transactions for perticular store with item code

what i want is, i want to take items details from itemMaster and items for perticular store from both ItemWh and whWiseItem.

all the tables having ItemCode.

i know how to combine two tables using JOIN. but how can i combine 3 tables.

SELECT whWiseItem.ItemId,itemMaster.ItemDescription,
itemMaster.UnitPrice,
 FROM whWiseItem
 inner join itemMaster on whWiseItem.ItemID=itemMaster.ItemId 
where WhseId='001'

i want to take items in ItemWh which are not in whWiseItem table. please help me to do this im really confuse.

thank you all

[Vomster]

Add another join under the itemMaster join.

[baavgai]

First, let me just reformat this so I can look at it:

SELECT b.ItemId, b.ItemDescription, b.UnitPrice
	FROM whWiseItem a
		inner join itemMaster b
			on a.ItemID=b.ItemId 
	where a.WhseId='001'

Looks like itemMaster is really being filtered by whWiseItem. Let's just flip it:

SELECT a.ItemId, a.ItemDescription, a.UnitPrice
	FROM itemMaster a
		inner join iwhWiseItem b
			on a.ItemID=b.ItemId 
				and b.WhseId='001'

Now, what would happen if we did an outer join:

SELECT a.ItemId, a.ItemDescription, a.UnitPrice
	FROM itemMaster a
		left outer join iwhWiseItem b
			on a.ItemID=b.ItemId 
				and b.WhseId='001'

How do I find all the entries in itemMaster that aren't in iwhWiseItem? Look for items that just didn't match:

SELECT a.ItemId, a.ItemDescription, a.UnitPrice
	FROM itemMaster a
		left outer join iwhWiseItem b
			on a.ItemID=b.ItemId 
				and b.WhseId='001'
	where b.ItemId is null

That should work, if I understand your question.