Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

SQL - Join Help

My query is not picking up records when a row does not exist in the PHONE table.  I'm not sure how to include a proper JOIN on this table.
SELECT LID.LID, 
PERSON.HRLN, 
PERSON.HRFN,
PERSON.HRMI,
HA.STREETLINE1,
HA.STREETLINE2,
HA.CITY,
HA.STATE,
HA.ZIPCODE,
HA.COUNTRY,
L1.DESCRIPTION,
EE.HRDEPTDESC,
EE.HRDEPTID,
EMP2.CONFIG1,
EMP2.CONFIG2,
EMP2.BCT_GROUP,
EMP2.BCT_COORDINATOR,
PH.PHONENUM

FROM LID LID, PERSON PERSON, LOCATION L1, LID_LOC L2, HOME_ADDRESS HA, EMP2 EMP2, EMPLOYEE EE, PHONE PH
WHERE LID.LID=PERSON.LID 
AND HA.PERSONID = PERSON.PERSONID
AND EMP2.PERSONID = PERSON.PERSONID
AND EE.PERSONID = PERSON.PERSONID
AND PH.LID = LID.LID
AND PH.PRIMARY_PHONE = 'Y'
AND LID.LID_TYPE='PERSON'
AND LID.STATUS = 'A'
AND L1.LOCATIONID=L2.LOCATIONID AND L2.PERSONID=PERSON.PERSONID
AND ((PERSON.ENTITYID = 'LWS'
AND L1.LOCATIONID NOT IN (10551,10695, 10698))
OR (PERSON.ENTITYID = 'LHT'
AND L1.LOCATIONID IN (10539,10508,10509,10510,10511)))   
ORDER BY PERSON.HRLN

Open in new window

0
nightshadz
Asked:
nightshadz
1 Solution
 
Lee SavidgeCommented:
Because you need to be doing a left join not an inner join. Don't list your tables you're joining in the from statement, use proper join statements.

Lee
0
 
sshah254Commented:
Lee is right.

What database are you using?

Ss
0
 
nightshadzAuthor Commented:
MS SQL Server 2005

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ephraim WangoyaCommented:
try it like this
SELECT LID.LID, 
PERSON.HRLN, 
PERSON.HRFN,
PERSON.HRMI,
HA.STREETLINE1,
HA.STREETLINE2,
HA.CITY,
HA.STATE,
HA.ZIPCODE,
HA.COUNTRY,
L1.DESCRIPTION,
EE.HRDEPTDESC,
EE.HRDEPTID,
EMP2.CONFIG1,
EMP2.CONFIG2,
EMP2.BCT_GROUP,
EMP2.BCT_COORDINATOR,
PH.PHONENUM

FROM PERSON
inner join LID on LID.LID=PERSON.LID 
inner join HOME_ADDRESS HA on HA.PERSONID = PERSON.PERSONID
inner join EMP2 ON EMP2.PERSONID = PERSON.PERSONID
inner join EMPLOYEE EE ON EE.PERSONID = PERSON.PERSONID
left join PHONE PH on PH.LID = LID.LID 
inner join LID_LOC L2 on L2.PERSONID=PERSON.PERSONID
inner join LOCATION L1 on L1.LOCATIONID = L2.LOCATIONID 
where PH.PRIMARY_PHONE = 'Y'
AND LID.LID_TYPE='PERSON'
AND LID.STATUS = 'A'
AND ((PERSON.ENTITYID = 'LWS'
AND L1.LOCATIONID NOT IN (10551,10695, 10698))
OR (PERSON.ENTITYID = 'LHT'
AND L1.LOCATIONID IN (10539,10508,10509,10510,10511)))   
ORDER BY PERSON.HRLN

Open in new window

0
 
nightshadzAuthor Commented:
Still only returns data when there is a row in PHONE table.
0
 
Ephraim WangoyaCommented:
Check for null phone number as well
SELECT LID.LID, 
PERSON.HRLN, 
PERSON.HRFN,
PERSON.HRMI,
HA.STREETLINE1,
HA.STREETLINE2,
HA.CITY,
HA.STATE,
HA.ZIPCODE,
HA.COUNTRY,
L1.DESCRIPTION,
EE.HRDEPTDESC,
EE.HRDEPTID,
EMP2.CONFIG1,
EMP2.CONFIG2,
EMP2.BCT_GROUP,
EMP2.BCT_COORDINATOR,
PH.PHONENUM

FROM PERSON
inner join LID on LID.LID=PERSON.LID 
inner join HOME_ADDRESS HA on HA.PERSONID = PERSON.PERSONID
inner join EMP2 ON EMP2.PERSONID = PERSON.PERSONID
inner join EMPLOYEE EE ON EE.PERSONID = PERSON.PERSONID
left join PHONE PH on PH.LID = LID.LID 
inner join LID_LOC L2 on L2.PERSONID=PERSON.PERSONID
inner join LOCATION L1 on L1.LOCATIONID = L2.LOCATIONID 
where (PH.PRIMARY_PHONE = 'Y' or PH.PRIMARY_PHONE IS NULL)
AND LID.LID_TYPE='PERSON'
AND LID.STATUS = 'A'
AND ((PERSON.ENTITYID = 'LWS'
AND L1.LOCATIONID NOT IN (10551,10695, 10698))
OR (PERSON.ENTITYID = 'LHT'
AND L1.LOCATIONID IN (10539,10508,10509,10510,10511)))   
ORDER BY PERSON.HRLN

Open in new window

0
 
nightshadzAuthor Commented:
Works!  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now