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.
| Author |
Topic |
|
anchoredwisdom
Starting Member
22 Posts |
 Posted - 2012-07-11 : 05:49:54
|
| Table 1 CURR_STSLOB FP BRAND ITEM SUB_ITEM PART COST--------------------------------------------------------------LOB1 FP1 BRAND1 SKU1 10LOB1 FP1 BRAND1 SKU1 MOD1 20LOB1 FP1 BRAND1 SKU1 MOD1 PART1 30TABLE 2PRIOR_STSLOB FP BRAND ITEM SUB_ITEM PART COST-----------------------------------------------------------------LOB1 FP1 BRAND1 SKU1 MOD1 PART2 400LOB1 FP1 BRAND1 SKU1 MOD1 PART1 300LOB1 FP1 BRAND1 SKU1 MOD1 200LOB1 FP1 BRAND1 SKU1 100LOB1 FP1 BRAND1 SKU2 MOD2 PART2 10 Expected Output LOB ITEM SUB_ITEM PART CURRCOST PRIORCOST----------------------------------------------------------- LOB1 SKU1 10 100LOB1 SKU1 MOD1 20 200LOB1 SKU1 MOD1 PART1 30 300LOB1 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 workingSELECT PR.LOB,PR.ITEM,PR.SUB_ITEM,PR.PART,PR.COST,CR.COSTFROM PRIOR_STS PR LEFT JOIN CURR_STS CRON PR.LOB=CR.LOB ANDPR.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) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
subhaoviya
Posting Yak Master
135 Posts |
Posted - 2012-07-11 : 06:01:15
|
| you can use inner join instead of left join. on the columns LOB, sub_item |
 |
|
|
|
|
|
Issue with Join