SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Issue with Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anchoredwisdom
Starting Member

22 Posts

Posted - 07/11/2012 :  05:42:19  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

323 Posts

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

anchoredwisdom
Starting Member

22 Posts

Posted - 07/11/2012 :  08:33:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000