SQL Not Showing Rows when no record exists in a joined table

Question

I have a clientlist table a payments table and a calls table

I want to return a row consisting of most of the clientlist table the date and amount of last payment and same for date of last call.

I have written a query that almost (so not at all) works .. it works as expected as long as a payment and call exist on the joined tables. if one or other does not exist the query does not return a row.

what i would like is the client data returned regardless of payment and calls data.

SELECT clientlist.ID, clientlist.FirstName, clientlist.LastName, clientlist.`Herd Number`, clientlist.Address1, clientlist.Address2, clientlist.Address3, clientlist.County, 
clientlist.postcode, clientlist.Mobile, clientlist.AuditDue AS `Cert Expiry`,p1.dateofpayment,p1.amt ,c1.dateofcall, a1.link,a1.dateOfAudit

FROM agritxtdev.clientlist left join payments p1 

on clientlist.ID=p1.clientnum 

left join calls c1

on clientlist.id= c1.clientnum left join auditdate a1 on a1.client = clientlist.id

where p1.id = (Select MAX(id) from payments p2 where p1.clientnum=p2.clientnum) 

AND c1.id= (Select MAX(id) from calls c2 where c1.clientnum=c2.clientnum) 

order by clientlist.ID

I would really appriecate any help ye can give me with this one.

im fairly certain the my issue is in here but im lost as to another approach

where p1.id = (Select MAX(id) from payments p2 where p1.clientnum=p2.clientnum) 

AND c1.id= (Select MAX(id) from calls c2 where c1.clientnum=c2.clientnum)

thanks in advance Peter

Thank you so much ... so simple but my head hurts from hitting the desk .... — Peter Roche, Jul 30 '20 at 20:22
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jul 30 '20 at 20:48
Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jul 30 '20 at 20:48
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jul 30 '20 at 20:48

score 1 · Answer 1 · answered Jul 30 '20 at 20:19

Indeed, the issue is with the conditions in the where clause. Basically you need to move them to the on clause of the respective join. Otherwise the conditions are mandatory, and filter out rows for which the left join did not match:

select ...
from agritxtdev.clientlist 
left join payments p1 
    on  clientlist.id=p1.clientnum 
    and p1.id = (select max(id) from payments p2 where p1.clientnum=p2.clientnum) 
left join calls c1 
    on  clientlist.id= c1.clientnum 
    and c1.id= (select max(id) from calls c2 where c1.clientnum=c2.clientnum) 
left join auditdate a1 
    on  a1.client = clientlist.id
order by clientlist.id

score -1 · Answer 2 · answered Jul 30 '20 at 20:21

I would recommend a change of approach , it's not only your problem - it's also a very wasteful proc.

try this :

select max(Id) 'IdPay' into #tempPayments from payments group by clientnum
select max(Id) 'IdCalls' into #tempCalls from calls group by clientnum

SELECT your_fields
FROM agritxtdev.clientlist left join #tempPayments p1 
on (clientlist.ID = p1.IdPay OR p1.IdPay IS NULL)
left join #tempCalls c1 on (clientlist.id= c1.IdCalls or c1.IdCalls IS NULL)
left join auditdate a1 on a1.client = clientlist.id

drop table #tempCalls
drop table #tempPayments

SQL Not Showing Rows when no record exists in a joined table

2 Answers2