Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Issue with Join

Author  Topic 

anchoredwisdom
Starting Member

22 Posts

Posted - 2012-07-11 : 05:42:19
Table 1
CURR_STS

LOB FP BRAND ITEM SUB_ITEM PART COST
--------------------------------------------------------------
LOB1 FP1 BRAND1 SKU1 10
LOB1 FP1 BRAND1 SKU1 MOD1 20
LOB1 FP1 BRAND1 SKU1 MOD1 PART1 30


TABLE 2
PRIOR_STS

LOB FP BRAND ITEM SUB_ITEM PART COST
-----------------------------------------------------------------
LOB1 FP1 BRAND1 SKU1 MOD1 PART2 400
LOB1 FP1 BRAND1 SKU1 MOD1 PART1 300
LOB1 FP1 BRAND1 SKU1 MOD1 200
LOB1 FP1 BRAND1 SKU1 100
LOB1 FP1 BRAND1 SKU2 MOD2 PART2 10



Expected Output
LOB ITEM SUB_ITEM PART CURRCOST PRIORCOST
-----------------------------------------------------------

LOB1 SKU1 10 100
LOB1 SKU1 MOD1 20 200
LOB1 SKU1 MOD1 PART1 30 300
LOB1 SKU1 MOD1 PART2 400
------------------------------------------------------------



THe row which containts item=sku2 ( from table prior_sts) should not come as it is not present in cur_sts.

I tried this query but it is not working

SELECT PR.LOB,
PR.ITEM,
PR.SUB_ITEM,
PR.PART,
PR.COST,
CR.COST
FROM PRIOR_STS PR LEFT JOIN CURR_STS CR
ON
PR.LOB=CR.LOB AND
PR.ITEM=CR.ITEM AND
(CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) = (CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) AND
(CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) = (CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) WHERE EXISTS

( SELECT ‘X’ FROM CURR_STS C WHERE C.ITEM=PRIOR_STS.ITEM)

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-07-11 : 05:58:00
change "left join" to "inner join" and remove the where section
Go to Top of Page

anchoredwisdom
Starting Member

22 Posts

Posted - 2012-07-11 : 08:33:02
Apologies for posting again.
It happened as there was some error after i clicked on submit.

I got the solution and i am posting the query below.
SELECT PR.*,CR.COST AS CURRENT_COST FROM

(
SELECT LOB,
ITEM,
(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,
(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,
COST

FROM PRIOR_STS ) PR LEFT JOIN
(
SELECT LOB,
ITEM,
(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,
(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,
COST

FROM CURR_STS ) CR

ON
PR.LOB=CR.LOB AND
PR.ITEM=CR.ITEM AND
PR.SUBITEM=CR.SUBITEM AND
PR.PART=CR.PART
WHERE EXISTS (SELECT 1 FROM CURR_STS C WHERE C.ITEM=PR.ITEM)
ORDER BY COST

Go to Top of Page
   

- Advertisement -