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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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]
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
Post Reply