DECLARE @TA TABLE([purchaseitem] VARCHAR(1), [purchaseprice] INT, [time] VARCHAR(12))
INSERT @TA
SELECT 'a', 20, '05/04--05/12' UNION ALL
SELECT 'a', 12, '05/12--05/23' UNION ALL
SELECT 'b', 23, '05/01--05/25' UNION ALL
SELECT 'c', 15, '05/03--05/28'DECLARE @TB TABLE([salesitem] VARCHAR(1), [salesprice] INT, [time] VARCHAR(12))
INSERT @TB
SELECT 'a', 24, '05/04--05/12' UNION ALL
SELECT 'c', 26, '05/03--05/28' UNION ALL
SELECT 'd', 25, '05/03--05/23'DECLARE @TC TABLE([item] VARCHAR(1), [description] VARCHAR(6))
INSERT @TC
SELECT 'a', 'red' UNION ALL
SELECT 'b', 'orange' UNION ALL
SELECT 'c', 'red' UNION ALL
SELECT 'd', 'black' UNION ALL
SELECT 'e', 'black'SELECT T.*,[description]
FROM @TC AS C RIGHT JOIN (
SELECT ISNULL(purchaseitem,salesitem) AS item,
purchaseprice,
salesprice,
ISNULL(A.time,B.time) AS [time]
FROM @TA AS A FULL JOIN @TB AS B ON A.[purchaseitem]=B.[salesitem] AND A.[time]=B.[time]
) T
ON T.item=C.item
/*
item purchaseprice salesprice time description
---- ------------- ----------- ------------ -----------
a 20 24 05/04--05/12 red
a 12 NULL 05/12--05/23 red
b 23 NULL 05/01--05/25 orange
c 15 26 05/03--05/28 red
d NULL 25 05/03--05/23 black
*/
INSERT @TA
SELECT 'a', 20, '05/04--05/12' UNION ALL
SELECT 'a', 12, '05/12--05/23' UNION ALL
SELECT 'b', 23, '05/01--05/25' UNION ALL
SELECT 'c', 15, '05/03--05/28'DECLARE @TB TABLE([salesitem] VARCHAR(1), [salesprice] INT, [time] VARCHAR(12))
INSERT @TB
SELECT 'a', 24, '05/04--05/12' UNION ALL
SELECT 'c', 26, '05/03--05/28' UNION ALL
SELECT 'd', 25, '05/03--05/23'DECLARE @TC TABLE([item] VARCHAR(1), [description] VARCHAR(6))
INSERT @TC
SELECT 'a', 'red' UNION ALL
SELECT 'b', 'orange' UNION ALL
SELECT 'c', 'red' UNION ALL
SELECT 'd', 'black' UNION ALL
SELECT 'e', 'black'SELECT T.*,[description]
FROM @TC AS C RIGHT JOIN (
SELECT ISNULL(purchaseitem,salesitem) AS item,
purchaseprice,
salesprice,
ISNULL(A.time,B.time) AS [time]
FROM @TA AS A FULL JOIN @TB AS B ON A.[purchaseitem]=B.[salesitem] AND A.[time]=B.[time]
) T
ON T.item=C.item
/*
item purchaseprice salesprice time description
---- ------------- ----------- ------------ -----------
a 20 24 05/04--05/12 red
a 12 NULL 05/12--05/23 red
b 23 NULL 05/01--05/25 orange
c 15 26 05/03--05/28 red
d NULL 25 05/03--05/23 black
*/
from tc left join ta on tc.item=ta.purchaseitem
left join tb on tc.item=tb.salesitem and ta.time =tb.time
select a.item,b.purchaseprice,b.salesprice,c.time,a.description
from tc a join tb b on a.item=b.purchaseitem
left join ta c on c.purchaseitem=a.item
select a.item,b.purchaseprice,b.salesprice,c.time,a.description
from tc a join tb b on a.item=b.saleitem
left join ta c on c.purchaseitem=a.item