SQL Left Outer Join Problem

Posted by Vuyiswamb under Sql Server on 11/5/2014 | Points: 10 | Views : 1546 | Status : [Member] [MVP] [Administrator] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i have Join that i expect to give me 3 Records but it gives me to Records . There is a Record in Table A and in table "C" there are two records , i used a left outer Join and i only get two records instead of 3. i dont want to use a Union to do this. Below is an Example


CREATE TABLE #TABLEA 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)

INSERT INTO #TABLEA 
VALUES('047600055/R',NULL)


CREATE TABLE #TABLEC 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS001')


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS002')

SELECT * FROM   #TABLEA A 
left outer  JOIN   #TABLEC C 
ON  A.LIS_KEY  =  C.LIS_KEY     
WHERE A.LIS_KEY  = '047600055/R'

Thanks

Thank you for posting at Dotnetfunda
[Administrator]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/6/2014 [Member] [MVP] Platinum | Points: 25

0	the below is only way you will get 3 records without using UNION... SELECT * FROM #TABLEA A FULL OUTER JOIN #TABLEC C ON A.LIS_KEY = C.LIS_KEY and a.FUNC_KEY = c.FUNC_KEY and A.LIS_KEY = '047600055/R' Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts