Solved

SQL - Join Help

Posted on 2011-02-11
7
260 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:ewangoya
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:
ewangoya 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 comma seperated list - SQL Server 12 44
Parsing the XML data to SQL Server 4 67
Inserting a column in a table that creates an ID and row number 4 65
SQL query with cast 38 53
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

829 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