SQL Left Outer Join Problem

Vimalsoft Pty Ltd

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com

Vimalsoft Pty Ltd

Got the Solution

SELECT *
FROM
(
SELECT * FROM #TABLEA A
UNION ALL
SELECT * FROM #TABLEC C
)t
WHERE LIS_KEY = '047600055/R'

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com

Mycroft Holmes

What happened to I don't want to use UNION :laugh:

Never underestimate the power of human stupidity RAH

Vimalsoft Pty Ltd

i take back my words :laugh:

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com

Richard Deeming

Two rows would be the expected result for a LEFT JOIN with that data. Each row from the table on the left will be returned once for each matching row in the table on the right, or once if there are no matching rows. Since the table on the right only has two rows, and the table on the left only has one row, the result will be two rows. Jeff Atwood has a good visual explanation of SQL joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/[^] Another way to explain joins:

Product the Cartesian Product of the two tables - each row in the left-hand table is matched with each row in the right-hand table.
Remove the rows where the JOIN condition is not met.
Depending on the JOIN type:
- INNER JOIN: Nothing to do.
- LEFT (OUTER) JOIN: Any rows in the left-hand table but not in the results are added back, matched with a row of NULL values for the right-hand table.
- RIGHT (OUTER) JOIN:: Any rows in the right-hand table but not in the results are added back, matched with a row of NULL values for the left-hand table.
- FULL (OUTER) JOIN: Apply the rules for both LEFT and RIGHT joins.
Filter the result based on the conditions in the WHERE clause (if any).

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer