Adding Sub Query Null Values - 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)

Adding Sub Query Null Values

Author

Topic

trsisko
Starting Member

2 Posts

Posted - 2008-10-06 : 04:45:35

I have the following query. Problem is that in the Sub Query there are members without a value and a Null value in the effdate. How can I adapt this to pull members with Null Value?

SELECT p.forename, p.reference, p.surname, p.PersonUID, s.line1, s.line2, s.line3, s.line4
FROM person p
left join address s
ON s.parentuid = p.personuid
inner join (
select parentuid, max(effdate) effdate1
from address
group by parentuid) addstripped
on s.parentuid = addstripped.parentuid
and s.effdate = addstripped.effdate1
where s.line1 is null
or s.line1 like 'X'
or s.line1 like '%Not known address'
or s.line1 like 'X'
or s.line1 like '%Address not known%'
or s.line1 like '%gone away%'
or s.line1 like '%no known%'
or s.line1 like '%unknown%'
or s.line1 like '%ADDRESSEE GONE AWAY%'
or s.line1 like '%DECEASCED'
or s.line1 like '%UNKNOWN ADDRESS-RETURNED MAIL'
or s.line1 like '%DECEASED'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 04:49:10

You INNER JOIN a LEFT JOIN table.

Continue using LEFT JOIN, or please tell us your business rules

SELECT		p.forename,
		p.reference,
		p.surname,
		p.PersonUID,
		s.line1,
		s.line2,
		s.line3,
		s.line4
FROM		person AS p
left join	address as s ON s.parentuid = p.personuid
left join	(
			select		parentuid,
					max(effdate) effdate1
			from		address
			group by	parentuid
		) addstripped on s.parentuid = addstripped.parentuid
			and s.effdate = addstripped.effdate1
where		s.line1 is null 
		or s.line1 like 'X'
		or s.line1 like '%Not known address'
		or s.line1 like 'X'
		or s.line1 like '%Address not known%'
		or s.line1 like '%gone away%'
		or s.line1 like '%no known%'
		or s.line1 like '%unknown%'
		or s.line1 like '%ADDRESSEE GONE AWAY%'
		or s.line1 like '%DECEASCED'
		or s.line1 like '%UNKNOWN ADDRESS-RETURNED MAIL'
		or s.line1 like '%DECEASED'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:49:46

[code]SELECT p.forename, p.reference, p.surname, p.PersonUID, s.line1, s.line2, s.line3, s.line4
FROM person p
left join address s
ON s.parentuid = p.personuid
left join (
select parentuid, max(effdate) effdate1
from address
group by parentuid) addstripped
on s.parentuid = addstripped.parentuid
and s.effdate = addstripped.effdate1
where s.line1 is null
or s.line1 like 'X'
or s.line1 like '%Not known address'
or s.line1 like 'X'
or s.line1 like '%Address not known%'
or s.line1 like '%gone away%'
or s.line1 like '%no known%'
or s.line1 like '%unknown%'
or s.line1 like '%ADDRESSEE GONE AWAY%'
or s.line1 like '%DECEASCED'
or s.line1 like '%UNKNOWN ADDRESS-RETURNED MAIL'
or s.line1 like '%DECEASED'[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:50:18

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources