Coping with JOIN failure? - SQL Server Forums

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)

Coping with JOIN failure?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 10:07:21

I have found that if I attempt a JOIN, and the external table contains either no matching data, or a null, my main SELECT query fails. What is the 'best practice' way to cope with this please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:11:48

OUTER JOIN.
But that depends on your business rules.

In some cases there should be no returning records and in some cases a zero value or empty space should be returned instead.

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

deepu_v04
Starting Member

3 Posts

Posted - 2008-02-07 : 10:13:10

you can use outer joins if you want to get some values from the other table

Sandeep

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 10:41:07

Thanks guys.

So if I have:

table1
------

code first_name last_name
123 Bill Smith
456 John Williams

table2
------
code description
456 Present
789 Not present

and I use

SELECT

t2.description AS code,
first_name,
last_name

FROM table1 AS t1

LEFT JOIN table2 AS t2 ON t1.code = t2.code

WHERE last_name = 'Williams'

would give me:

code first_name last_name
Present John Willams

How could I get:

SELECT

t2.description AS code,
first_name,
last_name

FROM table1 AS t1

LEFT JOIN table2 AS t2 ON t1.code = t2.code

WHERE last_name = 'Smith'

to give me:

code first_name last_name
123 Bill Smith

please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:46:57

[code]-- Prepare sample data
DECLARE @Table1 TABLE (Code SMALLINT, FirstName VARCHAR(20), LastName VARCHAR(20))

INSERT @Table1
SELECT 123, 'Bill', 'Smith' UNION ALL
SELECT 456, 'John', 'Williams'

DECLARE @Table2 TABLE (Code SMALLINT, Descr VARCHAR(20))

INSERT @Table2
SELECT 456, 'Present' UNION ALL
SELECT 789, 'Not present'

-- First attempt
SELECT t2.Descr AS Code,
t1.FirstName,
t1.LastName
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Code = t1.Code
WHERE t1.LastName = 'Williams'

-- This is perhaps what you want?
SELECT t2.Descr AS Code,
t1.FirstName,
t1.LastName
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Code IN (t1.Code, 789)
WHERE t1.LastName = 'Smith'[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 10:51:39

Try these:-

SELECT		t2.Descr AS Code,
		t1.FirstName,
		t1.LastName
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.Code IN (t1.Code, 789)
WHERE		t1.LastName = 'Smith'

SELECT		t2.Descr AS Code,
		t1.FirstName,
		t1.LastName
FROM		@Table1 AS t1
RIGHT JOIN	@Table2 AS t2 ON t2.Code IN (t1.Code, 789)
WHERE		t1.LastName = 'Smith'

SELECT		t2.Descr AS Code,
		t1.FirstName,
		t1.LastName
FROM		@Table1 AS t1
FULL OUTER JOIN	@Table2 AS t2 ON t2.Code IN (t1.Code, 789)
WHERE		t1.LastName = 'Smith'

and see if you can understand the difference between LEFT,RIGHT & FULL OUTER JOIN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:53:27

[code]-- This is perhaps what you want?
SELECT (SELECT TOP 1 t2.Descr FROM @Table2 AS t2 WHERE t2.Code IN (t1.Code, 789) ORDER BY CASE WHEN t1.Code = 798 THEN 1 ELSE 0 END) AS Code,
t1.FirstName,
t1.LastName
FROM @Table1 AS t1
WHERE t1.LastName = 'Williams'

-- This is perhaps what you want?
SELECT CASE
WHEN t2.Code IS NULL THEN 'Not present'
ELSE 'Present'
END AS Code,
t1.FirstName,
t1.LastName
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Code = t1.Code
WHERE t1.LastName = 'Smith'[/code]

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 11:40:47

quote:

Originally posted by Peso


-- This is perhaps what you want?
SELECT		CASE
			WHEN t2.Code IS NULL THEN 'Not present'
			ELSE 'Present'
		END AS Code,
		t1.FirstName,
		t1.LastName
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.Code = t1.Code
WHERE		t1.LastName = 'Smith'

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

Thanks Peso. That is heading in the right direction.
What I want is

If table2 contains a matching code then show it in t1.code else show the data held in t1.code (123) in code. Remembering that I don't know any of this data in advance. The example above assumes that I already know the possible data :'Present' or 'Not present'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 12:27:50

[code]SELECT CASE
WHEN t2.Code IS NOT NULL THEN t2.Code
ELSE t1.Code END AS Code,
t1.FirstName,
t1.LastName
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Code = t1.Code
WHERE t1.LastName = 'Smith'[/code]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 12:38:01

That's it Visakh16! Many thanks to all who replied.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-07 : 13:09:05

Did you dumb down you example? Your requirements do not make sense to me becaseu all you are asking for is data from one table (table1) without respect to its existence in the other table (table2).

SELECT 
	t1.Code,
	t1.FirstName,
	t1.LastName
FROM		
	@Table1 AS t1
WHERE	
	t1.LastName = 'Smith'

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 13:41:08

quote:

Originally posted by visakh16

SELECT		CASE
			WHEN t2.Code IS NOT NULL THEN t2.Code
			ELSE t1.Code 		END AS Code,
		t1.FirstName,
		t1.LastName
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.Code = t1.Code
WHERE		t1.LastName = 'Smith'

Consider using COALESCE(t2.Code, t1.Code) AS Code instead. Case statements are costly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources