left join with multiple arguments

[lajkonik86]

Heey,

I have this sql code:

SELECT comp.id, comp.name, count( c.id ) AS numbercoupons FROM csite_companies comp LEFT JOIN csite_coupons c ON c.company = comp.id WHERE comp.status =1 AND c.status =1 AND c.end >1173827267 AND c.start <1173827267 GROUP BY comp.id ORDER BY comp.name ASC

But im having a little problem. Somehow when there is nothing to link with in the csite_coupons table it doesn't select the company either. Since im using left join i would expect that it would do so. Is it because of my additional WHERE statements?

Thanks,
Thierry

[leatherback]

Well.. You require that certain fields in the coupon table have specific values (c.status=1, c.end<1173827267, c.start<....
So it is logical that if these fields are NULL, the related compagnies will not be selected. Not sure how to solve this. Possible you need to make them
(c.status =1 or c.status=NULL).

But there are people much better at this stuff than me. Sxooter or so will probably give a better solution.

[bogu]

Is not extracting the companies because if the company has no coupons then this condition will not be true

c.status =1 AND c.end >1173827267 AND c.start <1173827267 

Try something like this:

SELECT
    comp.id, comp.name, count( c.id ) AS numbercoupons
FROM csite_companies comp
LEFT JOIN csite_coupons c ON (c.company = comp.id AND c.status =1 AND c.end >1173827267 AND c.start <1173827267)
WHERE comp.status =1
GROUP BY comp.id ORDER BY comp.name ASC 

[lajkonik86]

thanks,

its more clear now, ill have a shot at it🙂

[bogu]

lajkonik86 wrote:

thanks,

its more clear now, ill have a shot at it🙂

tequila shot? :p