Join problem in query

Question

Post reply

Join problem in query

sk.panda

Right there with Babe

Points: 796
More actions
December 9, 2009 at 1:08 pm

#74205

Dear All,
Lets say below given the script i want to join with CHCM_TO_PSOFT_MAPPING and INBOUND_PLM_CHCM table.
if IPC.STATUS=CHCM.INCOMING_DATA then CHCM.OUTGOING_DATA AS 'STATUS'
else the condition does not satisfy then display IPC.STATUS
SELECT DISTINCT PER_NR,
CHCM.OUTGOING_DATA AS 'STATUS',
SOC_MAPPING
FROM
CHCM_TO_PSOFT_MAPPING CHCM --Master Table used for mapping the data
JOIN
INBOUND_PLM_CHCM IPC --Transaction Table
ON
IPC.STATUS=CHCM.INCOMING_DATA
WHERE
CHCM.PROCESS_TYPE='EZSUITE'
Please suggest how can i change the as per my requirement.
Thanks in advance.
Regards,
Sumanta Panda

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Try this:

SELECT DISTINCT

PER_NR,

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

SOC_MAPPING

FROM

CHCM_TO_PSOFT_MAPPING CHCM --Master Table used for mapping the data

LEFT OUTER JOIN INBOUND_PLM_CHCM IPC --Transaction Table

ON IPC.STATUS = CHCM.INCOMING_DATA

WHERE

CHCM.PROCESS_TYPE = 'EZSUITE'

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

Dear Sir,

For Example

SELECT PER_NR,STATUS,SOC_MAPPING FROM INBOUND_PLM_CHCM

12345 0 PLM-5000

12346 3 PLM-5001

12347 4 PLM-5002

12348 0 PLM-5000

34512 0 PLM-5000

SELECT PROCESS_TYPE,INBOUND_PLM_CHCM_FIELD,INCOMING_DATA,OUTGOING_DATA FROM CHCM_TO_PSOFT_MAPPING

EZSUITE STATUS0T

EZSUITE STATUS3A

In this below query you have suggested i am not getting the data properly.

SELECT DISTINCT

PER_NR,

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

SOC_MAPPING

FROM

CHCM_TO_PSOFT_MAPPING CHCM --Master Table used for mapping the data

LEFT OUTER JOIN INBOUND_PLM_CHCM IPC --Transaction Table

ON IPC.STATUS = CHCM.INCOMING_DATA

WHERE

CHCM.PROCESS_TYPE = 'EZSUITE'

My desired out should come like this..

12345TPLM-5000

12346APLM-5001

12348TPLM-5000

34512TPLM-5000

12347 0 PLM-5002

Please help.

Thanks for your time.

Regards,

Sumanta

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

Dear Lynn Pettis,

Please suggest me how i will get the correct output in your query.

Thanks,

Sumanta Panda

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

At this point all we can do is guess at what you are trying to accomplish. Help us help you, read the first article I reference below in my signature block regarding asking for assistance. Follow the instructions in that article for posting table structures and sample data. In addition, based on the sample data you post, post your expected results so we have something to test against.

You do that and you will get more help than you have so far, plus as a bonus, you will get tested code in return.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

if IPC.STATUS=CHCM.INCOMING_DATA then CHCM.OUTGOING_DATA AS 'STATUS'

else the condition does not satisfy then display IPC.STATUS

You've specified an INNER JOIN in your query. You will never see when the "condition does not satisfy". Change JOIN to LEFT JOIN then review Lynn's code.

Scrub this, didn't read enough. Nice work, Lynn.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

There was an irresistable whiff of buffalo chips in the air...

--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 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,

isnull(CHCM.OUTGOING_DATA, IPC.[STATUS]) 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'

/* desired output

12345TPLM-5000

12346APLM-5001

12347 0 PLM-5002

12348TPLM-5000

34512TPLM-5000

*/

Soooo... where is the Status of 0 to come from?

12347 0 PLM-5002

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Dear SSCommitted,

I want the Output Which should be like

/* desired output

12345TPLM-50000T

12346APLM-50013A

123474PLM-500244

12348TPLM-50000T

34512TPLM-50000T

*/

That means when the join condition not satify then display IPC.[STATUS] But not dispaly the null value.

Please suggest.

Thanks for your time.

Regards,

Sumanta

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

Grasshopper (aka sk.panda ),

Would like a correct answer or just shots in the dark?

If you want a correct answer, please read and follow the instructions in this article[/url] and post table structures and sample data. In addition, based on the sample data YOU provide, show us the expected results so we have something to test against.

In return for helping us help you, you will actually get tested code in return.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Lynn Pettis (12/10/2009)
Grasshopper (aka sk.panda ),
Would like a correct answer or just shots in the dark?
If you want a correct answer, please read and follow the instructions in this article[/url] and post table structures and sample data. In addition, based on the sample data YOU provide, show us the expected results so we have something to test against.
In return for helping us help you, you will actually get tested code in return.

Dear SSChampion

Couldn't have put it better 🙂 thanks! I reckon this one is answered IAC.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden