[RESOLVED] how to return a row even if no results returned

[cretaceous]

I've got an excessively long bit of SQL with lots of left joins and I want to know if it finds zero results

its a nested query inside another query so it's doubly messy

I know the first query will feed it a useful input.. I just want to show 'zeros' instead of a bald count of '0' as I'd get with with a mysql_num_rows count

hope that makes sense

(yes I know.. it was a nice clean query once upon a time but then the client said can it just do this and this and this and can we tie in something that has no match at the same time - aaargh )

[bpat1434]

If there are no matches, there are no rows... no rows, no result..... I'm gonna say no. You could use a CASE statement so that if something is empty you can return 0 in a certain column, but if rows aren't matched, they won't be added.

[cretaceous]

I think the problem is that I need to do a left join on the same table twice

here it is in all its ugliness:

SELECT centres.owner_id, centres.rgn_id, regions.rgn_id, regions.rgn_name,  owner.owner_id, owner.owner_name,
COUNT(bookings.bk_id) AS bkcount, start_d, end_d, bookings.bk_decline,
SUM(rental) AS renttot, SUM(outgoings) AS outgotot, SUM(gst) AS gsttot, SUM(commis) AS commistot
FROM  regions, customers, centres
LEFT JOIN bookings ON (bookings.ctr_ref_id='$sel_centre') 
LEFT JOIN statusnames ON (bookings.bk_status=statusnames.status_id)
LEFT JOIN owner ON (owner.owner_id=centres.owner_id)
WHERE regions.rgn_id=centres.rgn_id AND customers.cust_id=bookings.cust_id  AND centres.ctr_id='$sel_centre' 
AND (start_d>='$start_dd' ) AND (start_d <='$end_dd' )
AND bookings.bk_decline!=1 AND (bookings.bk_status>='2' AND bookings.bk_status<='4') AND customers.selfbook!=1 
GROUP BY bookings.ctr_ref_id 

.. so if you follow.. there may be no bookings for an entry (the centre)-
if I can do another LEFT JOIN to the same table.. maybe I need to try LEFT JOIN on an alias

it almost works if I add ..OR table.foo='NULL' to most of the WHERE conditions

I just keep thinking it should work and I'm sure I've seen a Lars Berg solution

[cretaceous]

decided to do it on 'mysql_num_rows'

• meant excessive if statements elsewhere when all I wanted were zeros

but I think I may have been imagining a non-realistic outcome