SQL Left Outer Join Problem

[vuyiswamb]

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

[NeedSomeAnswers]

You example looks correct you SHOULD get 2 records, In fact it should make no difference if you do a right or left join with your data.

Left outer join essentially says get me the records that match ( on LIS_KEY ) between tableA and TableC and also return me the records that exists in TableA but NOT TableC

A way to test this would be add another record with a different Lis_Key in TableA

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

Now do your select without your where clause;

right Join bring you 2 records

SELECT * FROM #TABLEA A
right JOIN #TABLEC C
ON A.LIS_KEY = C.LIS_KEY

left join and you will get 3 records.

SELECT * FROM #TABLEA A
left JOIN #TABLEC C
ON A.LIS_KEY = C.LIS_KEY

You don't actually need the Inner and Outer key words and i feel they just confuse things.

[TysonLPrice]

Not asking why you are trying to do that, just answering your question about how to get all three records without a UNION this works:

If object_id('tempdb..#TableAll') is not null drop table #TableAll
select * into #TableAll from #TABLEA;
insert into #TableAll select * from #TableC
select * from #TableAll
WHERE LIS_KEY  = '047600055/R'

Does it make sense is another question. What's wrong with a UNION?