MySQL Possible without sub-select? 2 Left Join related count
Posted: Wed Aug 26, 2009 9:53 am
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:
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
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` DESCthe 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