December 15, 2009 at 7:21 am
Dear ALL,
I have a trasaction INBOUND_PLM_CHCM table and Master mapping table CHCM_TO_PSOFT_MAPPING .
The Trasaction table INBOUND_PLM_CHCM contains 7000 records.
While writting below given query i am getting 7016 records.
even if i am SELECT count(PER_NR) from INBOUND_PLM_CHCM //7000 records.PER_NR primary key.
I dont understand in the below given query why i am getting 16 extra record.
Could you please tell me what might be the root cause for the problem.
SELECT DISTINCT IPC.PER_NR,
ISNULL(CHCM.OUTGOING_DATA,IPC.LOA_LEVEL) AS 'Managerlevel'
FROM
INBOUND_PLM_CHCM IPC
LEFT OUTER JOIN CHCM_TO_PSOFT_MAPPING CHCM
ON IPC.LOA_LEVEL=CHCM.INCOMING_DATA AND CHCM.PROCESS_TYPE = 'EZX'
Please suggest.
Thanks in advance.
Regards,
Sumanta
December 15, 2009 at 7:45 am
Are you sure the table contains only 7000 rows? Or are you looking at rows that have some value with a where clause?
If there are only 7000 rows, I'm confused too.
December 15, 2009 at 8:01 am
I might be missing something, but if you are joining the table to another table, then you can get more records then you have in the original table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 15, 2009 at 8:09 am
SELECT DISTINCT IPC.PER_NR,
ISNULL(CHCM.OUTGOING_DATA,IPC.LOA_LEVEL) AS 'Managerlevel'
FROM
INBOUND_PLM_CHCM IPC
LEFT OUTER JOIN CHCM_TO_PSOFT_MAPPING CHCM
ON IPC.LOA_LEVEL=CHCM.INCOMING_DATA AND CHCM.PROCESS_TYPE = 'EZX'
Yes, I am with Adi, there could be more than one record for a IPC.LOA_LEVEL =CHCM.INCOMING_DATA in your right table CHCM_TO_PSOFT_MAPPING
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply