2 Replies - 728 Views - Last Post: 15 June 2010 - 07:24 AM Rate Topic: -----

#1 buterfly0707  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 14-November 09

Join 3 tables

Posted 14 June 2010 - 09:08 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Join 3 tables

#2 Vomster  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 198
  • Joined: 15-May 08

Re: Join 3 tables

Posted 15 June 2010 - 07:00 AM

Add another join under the itemMaster join.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: Join 3 tables

Posted 15 June 2010 - 07:24 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1