Joining problem in query

Question

Post reply

Joining problem in query

sk.panda

Right there with Babe

Points: 796
More actions
December 10, 2009 at 1:31 pm

#75545

Dear All,
--Transaction Table
CREATE TABLE #INBOUND_PLM_CHCM (PER_NR INT, [STATUS] INT, SOC_MAPPING VARCHAR(10))
INSERT INTO #INBOUND_PLM_CHCM (PER_NR, [STATUS], SOC_MAPPING)
SELECT 12345, 0, 'PLM-5000' UNION ALL
SELECT 12346, 3, 'PLM-5001' UNION ALL
SELECT 12347, 4, 'PLM-5002' UNION ALL
SELECT 12348, 0, 'PLM-5000' UNION ALL
SELECT 12349, NULL, 'PLM-5000' UNION ALL
SELECT 34512, 0, 'PLM-5000'
SELECT * FROM #INBOUND_PLM_CHCM
---------------------------------------
--Master Table used for mapping the data
CREATE TABLE #CHCM_TO_PSOFT_MAPPING
(PROCESS_TYPE VARCHAR(10), INBOUND_PLM_CHCM_FIELD VARCHAR(10), INCOMING_DATA INT, OUTGOING_DATA CHAR(1))
INSERT INTO #CHCM_TO_PSOFT_MAPPING (PROCESS_TYPE, INBOUND_PLM_CHCM_FIELD, INCOMING_DATA, OUTGOING_DATA)
SELECT 'EZSUITE', 'STATUS', 0, 'T' UNION ALL
SELECT 'EZSUITE', 'STATUS', 3, 'A'
SELECT * FROM #CHCM_TO_PSOFT_MAPPING
----------------------------------------------
SELECT
IPC.PER_NR,
CHCM.OUTGOING_DATA AS 'STATUS',
IPC.SOC_MAPPING,
IPC.[STATUS],
CHCM.OUTGOING_DATA
FROM #INBOUND_PLM_CHCM IPC --Transaction Table
LEFT OUTER JOIN #CHCM_TO_PSOFT_MAPPING CHCM --Master Table used for mapping the data
ON IPC.[STATUS] = CHCM.INCOMING_DATA AND CHCM.PROCESS_TYPE = 'EZSUITE'
Please suggest using #INBOUND_PLM_CHCM,#CHCM_TO_PSOFT_MAPPING table join I want the Output Which should be like
/* desired output
12345TPLM-50000T
12346APLM-50013A
123474PLM-500244
12348TPLM-50000T
12349NULLPLM-5002NULLNULL
34512TPLM-50000T
*/
Thanks in advance.
Regards,
Sumanta

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The following meets your requirements:

CREATE TABLE #INBOUND_PLM_CHCM (PER_NR INT, [STATUS] INT, SOC_MAPPING VARCHAR(10))

INSERT INTO #INBOUND_PLM_CHCM (PER_NR, [STATUS], SOC_MAPPING)

SELECT 12345, 0, 'PLM-5000' UNION ALL

SELECT 12346, 3, 'PLM-5001' UNION ALL

SELECT 12347, 4, 'PLM-5002' UNION ALL

SELECT 12348, 0, 'PLM-5000' UNION ALL

SELECT 12349, NULL, 'PLM-5000' UNION ALL

SELECT 34512, 0, 'PLM-5000'

CREATE TABLE #CHCM_TO_PSOFT_MAPPING

(PROCESS_TYPE VARCHAR(10), INBOUND_PLM_CHCM_FIELD VARCHAR(10), INCOMING_DATA INT, OUTGOING_DATA CHAR(1))

INSERT INTO #CHCM_TO_PSOFT_MAPPING (PROCESS_TYPE, INBOUND_PLM_CHCM_FIELD, INCOMING_DATA, OUTGOING_DATA)

SELECT 'EZSUITE', 'STATUS', 0, 'T' UNION ALL

SELECT 'EZSUITE', 'STATUS', 3, 'A'

SELECT * FROM #INBOUND_PLM_CHCM

SELECT * FROM #CHCM_TO_PSOFT_MAPPING

SELECT

IPC.PER_NR,

ISNULL(CHCM.OUTGOING_DATA, IPC.STATUS) AS 'STATUS',

IPC.SOC_MAPPING,

IPC.[STATUS],

ISNULL(CHCM.OUTGOING_DATA, IPC.STATUS) AS OUTGOING_DATA

FROM

#INBOUND_PLM_CHCM IPC --Transaction Table

LEFT OUTER JOIN #CHCM_TO_PSOFT_MAPPING CHCM --Master Table used for mapping the data

ON IPC.[STATUS] = CHCM.INCOMING_DATA AND CHCM.PROCESS_TYPE = 'EZSUITE'

sk.panda Right there with Babe Points: 796 More actions · Answer 2

Thank you very much Lynn Pettis!!!.

Now It is working properly.

Regards,

Sumanta

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Isn't amazing what happens when you finally provide what you have been asked for several times? 🙂

That is why I kept asking you to read the article and provide the table definitions and sample data, AND the expected results. And in return, you got tested code instead of just shots in the dark.

Remember that the next time you post a question regarding a sql problem.