-
Can somebody please help me with this complicated query
Write a SQL query to reproduce vwProduct from tblProduct and tblProductOverride. You must use a single query. No Stored procedures, cursors or user defined functions.
vwProduct
ProductID ProductName ProductPrice ProductColor
1 New Name 1 11 New Color 1
2 New Name 2 2 Color 2
3 Name 3 3 Color 3
5 Name 5 5 Color 5
tblProduct
ProductID ProductName ProductPrice ProductColor RecordStatus
1 Name 1 1 Color 1 A
2 Name 2 2 Color 2 A
3 Name 3 3 Color 3 A
4 Name 4 4 Color 4 D
5 Name 5 5 Color 5 A
tblProductOverride
ProductOverrideID ProductID ProductName ProductPrice ProductColor RecordStatus
1 1 New Name 1 11 New Color 1 A
2 2 New Name 2 NULL NULL A
3 3 NULL 12 NULL D
-
Reply to query in a single query -
select O.ProductID, O.ProductName, isnull(O.ProductPrice, P.ProductPrice), isnull(O.ProductColor,P.ProductColor)
from tblProduct P inner join
tblProductOverride O
on P.ProductID = O.ProductID where O.ProductName is not null
union
select P.ProductID, P.ProductName, P.ProductPrice, P.ProductColor
from tblProduct P
where ProductID not in (select ProductID from tblProductOverride where ProductName is not null)
and P.RecordStatus ='A'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|