CODEWITHSUNDEEP
mysql
azsl1326
azsl1326

Reputation: 1410

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 ='[email protected]'. 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 |[email protected]   |[email protected],[email protected] |
+-----+----------------+---------------+---------------------+
|101  | maintenance    |[email protected]   |[email protected]        |
+-----+----------------+---------------+---------------------+


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     |
+-----+-------------+-------------+---------------+----------+

Upvotes: 1

Views: 75

Answers (4)

ran88
ran88

Reputation: 105

azsl1326

this I write simple query for example, hope it helps,

select
a.id, a.lightbox_name, b.id, b.lightbox_id, b.asset_name, b.asset_id
from lightboxes_tbl a
join lightbox_assets_tbl b
on (a.id = b.lightbox_id)
where a.author = "[email protected]"

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

Join to the assts table:

select
  lb.id, lb.lightbox_name, lb.author, lb.authoried_viewers,
  sum(a.id is not null) asset_count
from lightboxes_tbl
left join lightbox_asset_tbl a
  on a.lightbox_id = lb.id
where author ='[email protected]'
group by lb.id, lb.lightbox_name, lb.author, lb.authoried_viewers

There's a little trick in there: sum() is used to count how many rows are not null in the asst table, which will produce a total of zero for light boxes that have no assets - something that count() won't do when using a left join.

BTW, in mysql a boolean result is 1 if true, 0 if false, so summing a condition neatly counts how many times it was true.

Upvotes: 1

peterm
peterm

Reputation: 92785

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 = '[email protected]'
 GROUP BY l.id

Output:

|  ID |  LIGHTBOX_NAME |       AUTHOR |    AUTHORIED_VIEWERS | TOTAL_ASSETS |
|-----|----------------|--------------|----------------------|--------------|
| 100 | aircraft-types | [email protected] | [email protected],[email protected] |            2 |
| 101 |    maintenance | [email protected] |        [email protected] |            1 |

Here is a SQLFiddle demo

Recommended reading:

Upvotes: 2

arythiafan
arythiafan

Reputation: 40

You probably want something along the lines of this query...

SELECT LBT.id, LBT.lightbox_name, LBT.author, LBTA.id, LBTA.asset_name, LBTA.asset_path, LBTA.asset_id FROM lightboxes_tbl LBT JOIN lightbox_assets_tbl LBTA ON LBTA.lightbox_id = LBT.id WHERE author = '[email protected]'

Upvotes: 0

Related Questions