nightshadz
asked on
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
Lee is right.
What database are you using?
Ss
What database are you using?
Ss
ASKER
MS SQL Server 2005
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
ASKER
Still only returns data when there is a row in PHONE table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works! Thanks!
Lee