Need help on query

Question

Need help on query

cutecrazyguy

SSC Veteran

Points: 241
More actions
September 28, 2010 at 2:53 am

#238902

Hi all..
here my second post, thank's for helping me solve my problem before.
I've a problem on this query :
SELECT a.PerFirstJobDate, a.PerFirstName, c.JouDateEnd
FROM UDPersonnel a, UDJob b, UCJobOrgUnit c
WHERE a.PerFirstJobDate <> NULL AND a.PerPK = b.JobPerFK AND b.JobPK = c. JouJobFK AND c.JouDateEnd between '2005-01-01' AND '2010-12-30'
it resulting something like this :
==============================================
PerFirstJobDate || PerFirstName || JouDateEnd
==============================================
1965-12-05 || Gerrit || 2007-12-31
NULL || Jan || 2007-02-28
1965-12-05 || Gerrit || 2205-03-01
NULL || Klaas || 2010-05-31
i want to eliminate NULL on PerFirstJobDate column and add and extra condition so my Query going into Like this.
SELECT a.PerFirstJobDate, a.PerFirstName, c.JouDateEnd
FROM UDPersonnel a, UDJob b, UCJobOrgUnit c
WHERE a.PerFirstJobDate <> NULL AND a.PerPK = b.JobPerFK AND b.JobPK = c. JouJobFK AND c.JouDateEnd between '2005-01-01' AND '2010-12-30' AND a.PerFirstJobDate <> NULL
and resulting nothing but onLy the coLumn name.
is there anything wrong with my query ? thank you for the help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You cannot use = and <> when comparing to NULL. Such comparisons always return UNKNOWN (assuming default ansi_null settings)

Try this (formatted so that it's easier to read)

SELECT a.PerFirstJobDate, a.PerFirstName, c.JouDateEnd

FROM UDPersonnel a

INNER JOIN UDJob b ON a.PerPK = b.JobPerFK

INNER JOIN UCJobOrgUnit c b.JobPK = c. JouJobFK

WHERE a.PerFirstJobDate IS NOT NULL

AND c.JouDateEnd between '2005-01-01' AND '2010-12-30'

AND a.PerFirstJobDate IS NOT NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cutecrazyguy SSC Veteran Points: 241 More actions · Answer 2

Thank you for helping, it's work for me.

once again thank alot.

Chow's SSC Veteran Points: 296 More actions · Answer 3

Hi this is also the same solution but in your cup.

SELECT a.PerFirstJobDate, a.PerFirstName, c.JouDateEnd

FROM UDPersonnel a, UDJob b, UCJobOrgUnit c

WHERE a.PerPK = b.JobPerFK AND b.JobPK = c. JouJobFK

AND and a.PerFirstJobDate is NOT NULL

AND c.JouDateEnd between '2005-01-01' AND '2010-12-30'

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

Complete side point, but note the difference between how Gail had you do the table joins and how you were doing them in the original query. What you're doing is a very old style of join and you should work on learning the newer style (I hesitate to call it new since it's been around since 1992).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning