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!
| ID | LIGHTBOX_NAME | AUTHOR | AUTHORIED_VIEWERS | TOTAL_ASSETS | |-----|----------------|--------------|----------------------|--------------|
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