Solved

SQL - Join Help

Posted on 2011-02-11
7
263 Views
Last Modified: 2012-08-13
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
Comment
Question by:nightshadz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 34872948
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
 
LVL 9

Expert Comment

by:sshah254
ID: 34872991
Lee is right.

What database are you using?

Ss
0
 

Author Comment

by:nightshadz
ID: 34873025
MS SQL Server 2005

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34873063
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
 

Author Comment

by:nightshadz
ID: 34873086
Still only returns data when there is a row in PHONE table.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 34873110
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
 

Author Comment

by:nightshadz
ID: 34873152
Works!  Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled taskā€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question