SQL Left Outer Join Problem

Posted by Vuyiswamb under Sql Server on 11/5/2014 | Points: 10 | Views : 653 | Status : [Member] [MVP] [Administrator] | Replies : 1
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]



Responses

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

Up
0
Down
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

Login to post response