Wednesday, 27 May 2015

Mysql: Return row count from second table using one query

I have the following two tables. I need to select all the lightboxes from lightboxes_tbl where author ='scott@co.com'. That's obviously the easy part. Where I am stuck is that I also want to select in the same query the number of assets in each lightbox. For example, 'aircraft-types' lightbox (id = 100 / lightbox_id = 100) would return 2 assets. The 'maintenance' lightbox (id = 101 / lightbox_id = 101) would return 1 asset.
Thanks!
lightboxes_tbl
+-----+----------------+---------------+---------------------+
|id   |lightbox_name   |author         |authoried_viewers    |
+-----+----------------+---------------+---------------------+
|100  | aircraft-types |scott@co.com   |jon@co.com,aj@co.com |
+-----+----------------+---------------+---------------------+
|101  | maintenance    |scott@co.com   |nicole@co.com        |
+-----+----------------+---------------+---------------------+


lightbox_assets_tbl
+-----+-------------+-------------+---------------+----------+
|id   |lightbox_id  |asset_name   |asset_path     | asset_id |
+-----+-------------+-------------+---------------+----------+
|1    |100          |a321.jpg     |project1/imgs/ | 3700     |
+-----+-------------+-------------+---------------+----------+
|2    |100          |b757.jpg     |project1/imgs/ | 3444     |
+-----+-------------+-------------+---------------+----------+
|3    |101          |engine.jpg   |project4/imgs/ | 1444     |
+-----+-------------+-------------+---------------+----------+
 
Solution:
Make use of LEFT JOIN and COUNT()
SELECT l.*, COUNT(a.lightbox_id) total_assets FROM lightboxes_tbl l LEFT JOIN lightbox_assets_tbl a ON l.id = a.lightbox_id WHERE l.author = 'scott@co.com' GROUP BY l.id Output:
| ID | LIGHTBOX_NAME | AUTHOR | AUTHORIED_VIEWERS | TOTAL_ASSETS | |-----|----------------|--------------|----------------------|--------------|
 | 100 | aircraft-types | scott@co.com | jon@co.com,aj@co.com | 2 | 
| 101 | maintenance | scott@co.com | nicole@co.com | 1 |
 

0 comments:

Post a Comment