I've been struggling with this for quite some time. I have a SQLFiddle with the same approximate contents of this question.
I have three tables, items, profiles, and the linking table, posts that keys from the former two tables, schema with example data:
create table items (
item_id int unsigned primary key auto_increment,
title varchar(255)
);
insert into items (item_id, title) VALUES(1, 'Item One');
insert into items (item_id, title) VALUES(2, 'Item Two');
insert into items (item_id, title) VALUES(3, 'Item Three');
insert into items (item_id, title) VALUES(4, 'Item Four');
insert into items (item_id, title) VALUES(5, 'Item Five');
create table profiles (
profile_id int unsigned primary key auto_increment,
profile_name varchar(255)
);
insert into profiles (profile_id, profile_name) VALUES(1, 'Bob');
insert into profiles (profile_id, profile_name) VALUES(1, 'Mark');
insert into profiles (profile_id, profile_name) VALUES(1, 'Nancy');
create table posts (
post_id int unsigned primary key auto_increment,
item_id int unsigned, -- Relates to items.item_id
profile_id int unsigned, -- Relates to profile.profile_id,
post_date DATETIME
);
insert into posts (item_id, profile_id, post_date) values(1, 1, NOW());
insert into posts (item_id, profile_id, post_date) values(2, 2, NOW());
insert into posts (item_id, profile_id, post_date) values(2, 2, NOW());
I am using the following query to produce nearly-correct results:
SELECT
`items`.`item_id`,
`items`.`title`,
`profiles`.`profile_id`,
`profiles`.`profile_name`,
`posts`.`post_id`,
`posts`.`post_date`
FROM `items`
CROSS JOIN `profiles`
LEFT JOIN `posts` ON `items`.`item_id` = `posts`.`item_id`
AND `posts`.`profile_id` = `profiles`.`profile_id`;
For my particular application this is sub-optimal. I get a lot of 'extra' rows that my particular implementation does not need. The end result looks something like this:
+------------|------------|---------|-----------+
| Item Name | Profile ID | Post ID | Post Date |
+------------+------------+---------+-----------+
| Item One | 1 | 1 | 2015-... | -- Bob Posted this
| Item One | 2 | NULL | NULL | -- No one else did
| Item One | 3 | NULL | NULL |
| Item Two | 1 | 2 | 2015-... | -- Bob posted this
| Item Two | 2 | 3 | 2015-... | -- So did mark
| Item Two | 3 | NULL | NULL | -- Nancy didn't
| Item Three | 1 | NULL | NULL |
| Item Three | 2 | NULL | NULL |
| Item Three | 3 | 4 | 2015-... | -- Only nancy posted #3
| Item Four | 1 | NULL | NULL | -- No one posted #4
| Item Four | 2 | NULL | NULL |
| Item Four | 3 | NULL | NULL |
| Item Five | 1 | NULL | NULL | -- No one posted #5
| Item Five | 2 | NULL | NULL |
| Item Five | 3 | NULL | NULL |
+------------+------------+---------+-----------+
This is doing exactly as I requested - each item is returned three times (to correspond to the profile count). However it would be ideal if in the case of Items #4 and #5, where there is NO linkage, that they only be returned one time, with a NULL profile_id, as below:
+------------|------------|---------|-----------+
| Item Name | Profile ID | Post ID | Post Date |
+------------+------------+---------+-----------+
| Item One | 1 | 1 | 2015-... | -- Bob Posted this
| Item One | 2 | NULL | NULL | -- No one else did
| Item One | 3 | NULL | NULL |
| Item Two | 1 | 2 | 2015-... | -- Bob posted this
| Item Two | 2 | 3 | 2015-... | -- So did mark
| Item Two | 3 | NULL | NULL | -- Nancy didn't
| Item Three | 1 | NULL | NULL |
| Item Three | 2 | NULL | NULL |
| Item Three | 3 | 4 | 2015-... | -- Nancy posted #3
| Item Four | NULL | NULL | NULL | -- **No one posted #3 and #4
| Item Five | NULL | NULL | NULL | -- Only need #3 and #4 once**
+------------+------------+---------+-----------+
While in this example this only translates to 4 less rows, in my real-world application, there are many items, but not many profiles and posts. So this small change could reduce server-side language processing significantly.
Could anyone point me in the correct direction limit the cross join only where I have some type of linkage?
SELECT `items`.`item_id`,
`items`.`title`,
`profiles`.`profile_id`,
`profiles`.`profile_name`,
`posts`.`post_id`,
`posts`.`post_date`
FROM `items`
LEFT JOIN
`profiles`
ON EXISTS
(
SELECT NULL
FROM `posts`
WHERE `posts`.`item_id` = `items`.`item_id`
)
LEFT JOIN
`posts`
ON `items`.`item_id` = `posts`.`item_id`
AND `posts`.`profile_id` = `profiles`.`profile_id`
ORDER BY
`items`.`item_id`, `profiles`.`profile_id`