Page 1 of 1

MySQL Possible without sub-select? 2 Left Join related count

Posted: Wed Aug 26, 2009 9:53 am
by batfastad
Hi everyone
Before I go looking down the sub-select route, thought I'd check here to make sure I wasn't missing anything.

3 tables in an advertising booking system
- adbookings
- adinserts
- publications (a table of magazine titles)

adbookings is related to adinserts by a booking_id
publications is related to adinserts by a publication_id
... so adinserts is the sort of middle table in this relationship but I'm trying to get

adinserts has a BOOL field called cancelled
publications has a BOOL field called published

What I'm trying to do is get a list of the adbookings, with an extra column which shows the number of related adinserts that have cancelled IS NULL AND published IS NULL

Here's what I've got so far:

Code: Select all

SELECT `adbookings`.*, `adinserts`.*, COUNT(`adbookings`.`booking_id`) AS `adinserts_count`, COUNT(`adinserts`.`cancelled`) AS `cancel_count`, SUM(`publications`.`published`) AS `published_count` FROM `adbookings` LEFT JOIN `adinserts` USING(`booking_id`) LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id` WHERE `adbookings`.`company_id`=$company_id GROUP BY `adbookings`.`booking_id` ORDER BY `adbookings`.`booking_stamp` DESC
So at the moment I've got as far as getting:
the total number of related adinserts... adinserts_count
the total number of cancelled related adinserts... cancel_count
the total number of related adinserts where the publication has been published... published_count

But I don't know how to get a figure for the number of related adinserts where adinserts.cancelled IS NULL AND its related publications.published IS NULL

Any ideas?
I hope I've explained this clearly... I'm having a bit of trouble visualising it myself

Cheers, B

Re: MySQL Possible without sub-select? 2 Left Join related count

Posted: Wed Aug 26, 2009 1:19 pm
by Eran
I didn't completely understand your requirement, mainly since you didn't say what those tables actually do. You can get what you need by adding a case statement inside the sum -
[sql]SUM(    CASE WHEN adinserts.cancelled IS NULL AND publications.published IS NULL     THEN 1     ELSE 0    END) AS notpublished_count[/sql]

Re: MySQL Possible without sub-select? 2 Left Join related count

Posted: Wed Aug 26, 2009 2:31 pm
by batfastad
Fantastic! :lol:
I thought about trying to get some sort of IF() going on in there but couldn't work out if it would actually do what I wanted.
Just tried your code and it seems to work!
Thanks for the help - sorry for the bad explanation!
Cheers, B