• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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

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
TrialUser
Asked:
TrialUser
2 Solutions
 
Pratima PharandeCommented:
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
 
sachinpatil10dCommented:
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
 
DavidMorrisonCommented:
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
 
TrialUserAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now