where this = that or return null for table2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

where this = that or return null for table2

Author

Topic

Project-Mayu
Starting Member

5 Posts

Posted - 2007-10-17 : 01:24:47

i want to join two tables, but i want everything from table1 and only if matching, the values from table 2, if the where does not match with table 2, then i need empty null values instead for table2 columns.

And this must all work in one sql statement.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:34:50

[code]SELECT Col1, Col2, ...
FROM table1 AS T1
LEFT OUTER JOIN table2 AS T2
ON T2.SomeKey = T1.SomeKey
[/code]
Kristen

Project-Mayu
Starting Member

5 Posts

Posted - 2007-10-17 : 01:42:32

That removes, the rows that don't have a value in table 2
I need all rows in table 1

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:56:03

You will get ALL the rows from table 1

Kristen

Project-Mayu
Starting Member

5 Posts

Posted - 2007-10-17 : 02:03:26

out of the two rows in table 1, only one was displayed, the one that matched a column in table 2

Project-Mayu
Starting Member

5 Posts

Posted - 2007-10-17 : 02:12:49

example
table1
id, x, y, z, name
table2
stuff, Description

table1 contains
1, a, b, c, NULL
2, sg, sdgwseh, wehwe, abc
3. f, weg, dsfs, ter

Table2 contains
edrhjredfj, abc

Expected output for name = Description
1, a, b, c, NULL, NULL, NULL
2, sg, sdgwseh, wehwe, abc, edrhjredfj, abc
3. f, weg, dsfs, ter, NULL, NULL

Project-Mayu
Starting Member

5 Posts

Posted - 2007-10-17 : 02:28:16

got it to work, changed LEFT TO RIGHT

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 02:29:39

Did you ACTUALLY try it?


DECLARE @Table1 TABLE
(
	id int,
	x varchar(10), 
	y varchar(10),
	z varchar(10),
	name varchar(10)
)

DECLARE @Table2 TABLE
(
	stuff varchar(10), 
	Description varchar(10)
)

INSERT INTO @Table1
SELECT 1, 'a', 'b', 'c', NULL UNION ALL
SELECT 2, 'sg', 'sdgwseh', 'wehwe', 'abc' UNION ALL
SELECT 3, 'f', 'weg', 'dsfs', 'ter'

INSERT INTO @Table2
SELECT 'edrhjredfj', 'abc'

SELECT	*
FROM @Table1 AS T1
	LEFT OUTER JOIN @Table2 AS T2
		ON T2.[Description] = T1.[name]

id          x          y          z          name       stuff      Description 
----------- ---------- ---------- ---------- ---------- ---------- ----------- 
1           a          b          c          NULL       NULL       NULL
2           sg         sdgwseh    wehwe      abc        edrhjredfj abc
3           f          weg        dsfs       ter        NULL       NULL

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 02:47:01

Hard to believe that a "copy and paste" operation changed Kristens' orignal LEFT OUTER JOIN become an RIGHT OUTER JOIN!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources