Alexander Alexander - 3 months ago 7
MySQL Question

Combine results from 3 tables

Here is a simplified schema of my data :

CREATE TABLE IF NOT EXISTS `posts` (
`id` VARCHAR(64) NOT NULL,
`name` VARCHAR(64) NOT NULL,
`url` VARCHAR(255) NOT NULL,
`data_date` DATE NOT NULL,
PRIMARY KEY(`id`, `data_date`)
);

CREATE TABLE IF NOT EXISTS `post_views_by_id` (
`id` VARCHAR(64) NOT NULL,
`views` INT(16) NOT NULL DEFAULT 0,
`data_date` DATE NOT NULL,
PRIMARY KEY(`id`, `data_date`)
);

CREATE TABLE IF NOT EXISTS `post_views_by_url` (
`url` VARCHAR(255) NOT NULL,
`views` INT(16) NOT NULL DEFAULT 0,
`data_date` DATE NOT NULL,
PRIMARY KEY(`url`, `data_date`)
);


Example data :

INSERT INTO `posts`
(`id`, `name`, `url`, `data_date`)
VALUES
("1a", "name1", "url1", "2016-08-08"),
("1b", "name2", "url2", "2016-08-08"),
("1c", "name3", "url3", "2016-08-08"),
("1a", "name1", "url1", "2016-08-09"),
("1b", "name2", "url2", "2016-08-09"),
("1c", "name3", "url3", "2016-08-09");


INSERT INTO `post_views_by_id`
(`id`, `views`, `data_date`)
VALUES
("1a", 10, "2016-08-08"),
("1b", 15, "2016-08-08"),
("1a", 12, "2016-08-09"),
("1b", 17, "2016-08-09");


INSERT INTO `post_views_by_url`
(`url`, `views`, `data_date`)
VALUES
("url3", 22, "2016-08-08"),
("url3", 12, "2016-08-09");


I am trying to create a query which will return a list of posts with view numbers from the 3 tables combined.

When i do only one join ...

SELECT
p.id AS 'post_id',
p.name AS 'post_name',
p.url AS 'post_url',
pbi.views AS 'post_views',
p.data_date AS 'date'
FROM posts p
LEFT JOIN post_views_by_id pbi
ON p.id = pbi.id
WHERE p.data_date = "2016-08-08"
AND pbi.data_date = "2016-08-08"


i get a result :


id name url views data_date
1a name1 url1 10 August, 08 2016 00:00:00
1b name2 url2 15 August, 08 2016 00:00:00


Now i want to combine the third table, where views are stored with url.

SELECT
p.id AS 'post_id',
p.name AS 'post_name',
p.url AS 'post_url',
pbi.views AS 'post_views_id',
pbu.views AS 'post_views_url',
p.data_date AS 'date'
FROM posts p
INNER JOIN post_views_by_id pbi
ON p.id = pbi.id
INNER JOIN post_views_by_url pbu
ON p.url = pbu.url
WHERE p.data_date = "2016-08-08"
AND pbi.data_date = "2016-08-08"
AND pbu.data_date = "2016-08-08"


Then i got no results at all ..

I'm not really sure how to construct the right query. i have read many posts here on SO about multiple joins , but none of them really helped me to solve the problem.

The expected result should be :



id name url views data_date
1a name1 url1 10 August, 08 2016 00:00:00
1b name2 url2 15 August, 08 2016 00:00:00
1c name3 url3 22 August, 08 2016 00:00:00



SQL Fiddle for the question

Answer

If am not wrong you need to use LEFT OUTER JOIN instead of INNER JOIN

SELECT p.id        AS 'post_id', 
       p.NAME      AS 'post_name', 
       p.url       AS 'post_url', 
       pbi.views   AS 'post_views_id', 
       pbu.views   AS 'post_views_url', 
       p.data_date AS 'date' 
FROM   posts p 
       LEFT JOIN post_views_by_id pbi 
              ON p.id = pbi.id 
                 AND pbi.data_date = p.data_date 
       LEFT JOIN post_views_by_url pbu 
              ON p.url = pbu.url 
                 AND pbu.data_date = p.data_date 
WHERE  p.data_date = '2016-08-08'
Comments