Topic: [SOLVED] What's up with this query?

[Moron]

Code: [Select]

$RESULTDS=mssql_query("SELECT DISTINCT LH.[EMPNO], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[PAYCTR], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF], EH.[DATE], EH.[ENETPA], EH.[EGRSER], EH.[EREGHR], EH.[EDEDUC], EH.[EROTHR], EH.[EFWHD], EH.[EPEDAT], EH.[ESSDED], EH.[EHOSPD], EH.[ELIFED], EH.[ECRUD], M2.[POSITN], M2.[MCTDWH], M2.[MHDATE], M2.[MCTDCS], M2.[MO3TOT], M2.[MCTDLD], M2.[MCTDGE], M2.[MALPPP], M2.[MSLPPP], M2.[MWHSTA], M2.[MWHALL], M2.[MWHADD], EH.[EGARND], EI.[DDEPTN], EI.[NEWOCC], EI.[HRYRAT], EI.[MEMPAD], EI.[MEMPCS], EI.[MEMPZI]

FROM LeaveHistory LH

JOIN MASTERL2 M2
ON LH.[EMPNO]=M2.EMPNO  JOIN EARNHIST EH
ON EH.[EEMPNO]=M2.EMPNO JOIN View_EmployeeInfo EI
ON EI.[EMPNO]=M2.EMPNO

WHERE M2.[MSSNO] = '".$_SESSION['password']."' and EH.[EPEDAT] = '8252007'

ORDER BY EH.[DATE] desc");

$RESULT=mssql_fetch_assoc($RESULTDS);
When I enter my own info, it gives me what I want. It works the same for my boss. He and I have been here for several years. But, when I enter the info for one of the newer guys, it pulls nothing.

I directly queried M2 WHERE their SSN (password) matches and it shows. I queried EH WHERE the new guy's record has an EPEDAT of "8252007." Yet it pulls nothing on the new guys.

Thoughts?

[cmgmyr]

Instead of using just "JOIN" for some try "LEFT JOIN" this should do what you want it to.

[btherl]

The difference is that JOIN requires matching data in ALL joined tables.  LEFT JOIN requires only data in the left table.  If there is no matching data in the right table, blank data will be added.

Example:

SELECT * FROM employees JOIN phone ON (employees.empno = phone.empno)

If an employee has no phone number, then no data will be fetched here.  But

SELECT * FROM employees LEFT JOIN phone ON (employees.empno = phone.empno)

If an employee has no phone number, data WILL be fetched from the employees table only, and the columns from the phone table will be set to NULL.

[Moron]

Thanks, guys, but still no luck. I can directly query the table(s) on one of the newer guys and the records appear, but nothing when done through the PHP page.

It's weird.

[btherl]

Have you tried printing out your query from the php script (after all variable substitutions are done), copy and paste and run it manually?

[Moron]

Have you tried printing out your query from the php script (after all variable substitutions are done), copy and paste and run it manually?

I had done that with each individual table. It works now. I changed my "from" statement to a table that was assured of having entries for all employees and used LEFT JOIN.

Thanks, everyone!