Select results from table1 based on entries on table2

Question

I have 2 tables;

banner_views (id, b_id, b_date)- this record a banner view every time it gets displayed

banners_dynamic (id, status, static_iname, static_keywords, static_url, static_alt, static_type, static_image, b_views, b_clicks) - stores the banner data

I would like to select 3 banners_dynamic results which have had the least views in the last 7 days.

I did put somethign together (see below) but I realised it was grabbing the total views for all banner rather than uniquely by id.

SELECT  *, 
(SELECT COUNT(*) FROM banner_views v WHERE v.b_date >= DATE(NOW()) - INTERVAL 7 DAY) as post_count 
FROM banners_dynamic b  
WHERE static_keywords LIKE '%test%' AND b.status='1' AND b.static_type='1' 
ORDER BY post_count ASC LIMIT 3

Can anyone point me in the correct direction?

Answer 1

You must join both banners_dynamic table and your subquery with corresponding banner IDs:

SELECT
    b.*, p.b_count
FROM
    banners_dynamic b
    INNER JOIN (
        SELECT
            b_id,
            COUNT(*) AS b_count
        FROM
            banner_views v
        WHERE
            v.b_date >= DATE(NOW() - INTERVAL 7 DAY)
        GROUP BY
            b_id
    ) p on p.b_id = b.id
WHERE
    b.static_keywords LIKE '%test%'
    AND b.`status` = '1'
    AND b.static_type = '1'
ORDER BY
    p.b_count ASC
LIMIT 3

UPDATE: You can do it even without subquery:

SELECT
    b.*, COUNT(v.b_id) AS b_count
FROM
    banners_dynamic b
    INNER JOIN banner_views v ON v.b_id = b.id
WHERE
    v.b_date >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
    AND b.static_keywords LIKE '%test%'
    AND b.`status` = '1'
    AND b.static_type = '1'
GROUP BY
    v.b_id
ORDER BY
    b_count ASC
LIMIT 3;

If you want to include banners without any views (count=0) then you must do a LEFT JOIN:

SELECT
    b.*, COUNT(v.b_id) AS b_count
FROM
    banners_dynamic b
    LEFT JOIN banner_views v ON v.b_id = b.id
              AND v.b_date >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
WHERE
    b.static_keywords LIKE '%test%'
    AND b.`status` = '1'
    AND b.static_type = '1'
GROUP BY
    v.b_id
ORDER BY
    b_count ASC
LIMIT 3;