?
Solved

joining 2 tables that have 1 to any relationship and applying some conditions  in sql server 2008

Posted on 2011-10-12
4
Medium Priority
?
272 Views
Last Modified: 2012-05-12
I have 2 tables. One is:Item
columns :
ItemID Itemnmbr Price

Another table ItemException
Columns:
ItemID   StoreID PriceException

For each ItemID in Item table there could be no record in ItemPriceException or 1 record or multiple records in ItemPriceException.

Now what I want to do is
1) select all from item where there are no corresponding rows in ItemPriceexception
2)  if there is a row in ItemPriceexception with its corresponding store ID say 500, then return this record with price from ItemPriceexception
3) If there is a row in ItemPriceexception with storeid = 0, then this will supersede any other ItemPriceexception or the default price in the Item table.

What is the most efficient way to do this. Can I do this using 1 query, or may be get results in temp tables and then do Union?

Please help. Thanks.

0
Comment
Question by:TrialUser
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 total points
ID: 36955275
select all from item where there are no corresponding rows in ItemPriceexception


Select * from Item
where ItemID nit int ( select ItemID in ItemException)

if there is a row in ItemPriceexception with its corresponding store ID say 500, then return this record with price from ItemPriceexception


Select I.* , IE.StoreID, IE.Price from Item I
inner join ItemException IE on I.ItemId=IE.ItemID and IE.storeID <> 0

If there is a row in ItemPriceexception with storeid = 0, then this will supersede any other ItemPriceexception or the default price in the Item table.


Select I.* , IE.StoreID, IE.Price from Item I
inner join ItemException IE on I.ItemId=IE.ItemID and IE.storeID = 0

Now you want union of all this is it ??

Select Item.* NULL as StoreID , NULL as Price  from Item
where ItemID nit int ( select ItemID in ItemException)
Union
Select I.* , IE.StoreID, IE.Price from Item I
inner join ItemException IE on I.ItemId=IE.ItemID and IE.storeID <> 0
Union

Select I.* , IE.StoreID, IE.Price from Item I
inner join ItemException IE on I.ItemId=IE.ItemID and IE.storeID = 0
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36955280
Check this
create table Item (ItemID int, Itemnmbr int, Price decimal)
insert into Item 
select 2,201,20.05
union all select 1,	101,	10
union all select 4,	101,	10
create table ItemException (ItemID int,   StoreID int, PriceException decimal)
insert into ItemException 
select 3,301,19.05
union all select 2,	500,	20
union all select 2,	500,	21
union all select 4,	0,	21

--1  select all from item where there are no corresponding rows in ItemPriceexception

select I.* from Item I left outer Join ItemException E on I.ItemID = E.ItemID 
where E.ItemID is null
--2 if there is a row in ItemPriceexception with its corresponding store ID say 500, then return this record with price from ItemPriceexception

select I.ItemID, I.Itemnmbr, E.PriceException  from Item I left outer Join ItemException E on I.ItemID = E.ItemID 
where E.StoreID = 500
--3 If there is a row in ItemPriceexception with storeid = 0, then this will supersede any other ItemPriceexception or the default price in the Item table.

select I.ItemID, I.Itemnmbr, E.PriceException  from Item I left outer Join ItemException E on I.ItemID = E.ItemID 
where E.StoreID = 0

Open in new window

0
 
LVL 5

Assisted Solution

by:DavidMorrison
DavidMorrison earned 1000 total points
ID: 36955303
Hi, please try something like this:


SELECT I.ItemID, I.ItemNumber, IE.StoreID, COALESCE(IE_Master.PriceException, IE.PriceException, I.Price) AS Price
FROM Item AS I
LEFT OUTER JOIN ItemException AS IE
      ON I.ItemId = IE.ItemID
      AND IE.StoreId <> 0
LEFT OUTER JOIN ItemException AS IE_Master
      ON I.ItemId = IE_Master.ItemId
      AND IE.Master_StoreId = 0
0
 

Author Comment

by:TrialUser
ID: 36957842
Pratima:

so if query 2 returns results for a certain ID then will query 2 results not be outputted?

If 3 has results then only that price should be shown and not the one from query 1 or 2? Thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question