CODEWITHSUNDEEP
mysqlsql
jaypabs
jaypabs

Reputation: 1567

Select All Records From One Table And Matching Record From Other Table

I work for several hours just to get the result that I want on my SQL code but without luck. I'm trying to select all records from one table and matching records from other table.

Please see the live sample here.

url_id  short_url   campaign    Clicks
1       test1       campaign1   2
2       test2       campaign2   2

SQL code from above sample:

SELECT u.url_id, u.short_url, c.campaign, Count(*) AS Clicks
FROM (urls u LEFT JOIN log l ON l.url_id = u.url_id) LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
WHERE (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17'))
GROUP BY u.url_id, u.short_url, c.campaign;

What I want on the results is this. Include all records from urls table.

url_id  short_url   campaign    Clicks
1       test1       campaign1   2
2       test2       campaign2   2
3       test3       campaign1   0
4       test4                   0

I tried adding l.url_id is null in the WHERE clause but the clicks has a value of 1 instead of zero (0)

url_id  short_url   campaign    Clicks
1       test1       campaign1   2
2       test2       campaign2   2
3       test3       campaign1   1
4       test4                   1

SQL code from above sample:

SELECT u.url_id, u.short_url, c.campaign, Count(*) AS Clicks
FROM (urls u LEFT JOIN log l ON l.url_id = u.url_id) LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
WHERE (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17')) or l.url_id is null
GROUP BY u.url_id, u.short_url, c.campaign;

Upvotes: 1

Views: 3047

Answers (3)

Rafiqul Islam
Rafiqul Islam

Reputation: 1646

SELECT u.url_id, u.short_url, c.campaign, Count(c.campaign_id) AS Clicks
FROM urls u LEFT JOIN log l ON l.url_id = u.url_id 
LEFT JOIN campaign c ON u.campaign_id = c.campaign_id 
AND (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17')) 
GROUP BY u.url_id, u.short_url, c.campaign;

Check Demo

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

It might be conceptually easier to just aggregate over the log table, since this is what is generating the click counts, not the other tables. Going along those lines we can try the following query:

SELECT t1.url_id,
       t1.short_url,
       COALESCE(t2.campaign, 'NA') AS campaign,
       COALESCE(t3.num_clicks, 0) AS Clicks
FROM urls t1
LEFT JOIN campaign t2
    ON t1.campaign_id = t2.campaign_id
LEFT JOIN
(
    SELECT url_id, COUNT(*) AS num_clicks
    FROM log
    WHERE DATE_FORMAT(date_time, '%Y-%m-%d') BETWEEN '2017-03-14' AND '2017-03-17'
    GROUP BY url_id
) t3
    ON t1.url_id = t3.url_id;

Output:

enter image description here

Demo here:

Rextester

Upvotes: 3

Sangharsh
Sangharsh

Reputation: 3019

Use COUNT(l.id) in place of COUNT(*).

Add l.date_time IS NULL in WHERE clause.

Check http://rextester.com/YOX73980

SELECT u.url_id, u.short_url, c.campaign, Count(l.id) AS Clicks
FROM urls u 
LEFT JOIN log l ON l.url_id = u.url_id 
LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
WHERE l.date_time IS NULL OR (DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17'
GROUP BY u.url_id, u.short_url, c.campaign;

Upvotes: 2

Related Questions