joining - 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

General SQL Server Forums

New to SQL Server Programming

joining

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-08-28 : 00:26:22

select id,depart,arrive,code from tableA

id depart arrive code total
123 NULL NULL A 30
123 UK AUS B 80
123 AUS UK B 70

select id,depart,arrive,journey from tableB

id depart arrive journey
123 UK AUS 1
123 AUS UK 2

how can i apply/replace codeA which has null depart and arrive column to be same as tableB journey1?

expected result:

id depart arrive code total
123 UK AUS A 30
123 UK AUS B 80
123 AUS UK B 70

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 01:10:31

Whats the rule for getting values? why cant it be AUS UK instead of UK AUS? is it like next available value? if yes, is there a field to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jethrow
Starting Member

37 Posts

Posted - 2013-08-28 : 01:24:41

Whereas I agree with visakh16 that we need more info, here is a surface level answer that satisfies the OP:

SELECT	id,
	IsNull(depart, (Select depart From tableB Where journey=1)) As depart,
	IsNull(arrive, (Select arrive From tableB Where journey=1)) As arrive,
	code,
	total
FROM	tableA

Microsoft SQL Server Noobie

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-08-28 : 02:06:03

those which is NULL only will grab those with journey = 1

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-08-28 : 02:22:00

i can't use isNULL. the column which is blank suppose to be depart=''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 02:40:16

[code]
SELECT a.id,
COALESCE(NULLIF(a.depart,''),b.depart) As depart,
COALESCE(NULLIF(a.arrive,''),b.arrive) As arrive,
a.code,
a.total
FROM tableA a
LEFT JOIN TableB b
ON b.id = a.id
AND b.Journey=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-08-28 : 02:42:33

[code]
;WITH TableA AS(
SELECT *
FROM (VALUES(123,NULL,NULL,'A',30), (123,'UK','AUS','B',80), (123,'AUS','UK','B',70)) AS TableA(id, depart, arrive, code, total)
), TableB AS(
SELECT *
FROM (VALUES(123,'UK','AUS',1), (123,'AUS','UK',2)) AS TableB(id, depart, arrive, journey)
)
SELECT
TableA.id
, COALESCE(TableA.depart, TableB.depart)
, COALESCE(TableA.arrive, TableB.arrive)
, TableA.code
, TableA.total
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id
WHERE TableB.journey = 1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 02:57:34

quote:

Originally posted by waterduck


;WITH TableA AS(
	SELECT	*
	FROM	(VALUES(123,NULL,NULL,'A',30), (123,'UK','AUS','B',80), (123,'AUS','UK','B',70)) AS TableA(id, depart, arrive, code, total)
), TableB AS(
	SELECT	*
	FROM	(VALUES(123,'UK','AUS',1), (123,'AUS','UK',2)) AS TableB(id, depart, arrive, journey)
)
SELECT	
	TableA.id
,	COALESCE(TableA.depart, TableB.depart)
,	COALESCE(TableA.arrive, TableB.arrive)
,	TableA.code
,	TableA.total
FROM	TableA
		LEFT JOIN TableB
	ON TableA.id = TableB.id
WHERE	TableB.journey = 1

The WHERE condition will cause LEFT JOIN to reduce to an INNER JOIN. So you might need to specify the condition in ON clause

see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-08-28 : 03:16:30

noted, and reading urs article

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources