I would like a single query to return data from several tables, where there may be 0 or more results. LEFT JOIN, perfect! Except that if I use 2 LEFT JOINs in a single query, and there are several results in each of them, MSSQL returns the cartesian product of the subqueries.
I'll explain with an example: I have a TITLE table, which contains information about some titles. I have a HOOK table, which marks some positions of the titles with a special name. I have a STATS table, which records when titles have been played. I would like to retrieve the information from a title with its HOOK and STATS entries.
create database newtest
create table T_TITLE(NUM_TITLE INTEGER PRIMARY KEY, NAME VARCHAR(255));
create table T_STATS(NUM_STATS INTEGER PRIMARY KEY, NUM_TITLE INTEGER, DATE_BROADCAST DATETIME);
create table T_HOOK(NUM_HOOK INTEGER PRIMARY KEY, NUM_TITLE INTEGER, NAME VARCHAR(255));
insert into T_TITLE(NUM_TITLE,NAME) values (1,'name');
insert into T_STATS(NUM_STATS,NUM_TITLE,DATE_BROADCAST) values (10,1,'2015-07-20 00:00:00'),(11,1,'2015-07-20 01:00:00'),(12,1,'2015-07-20 02:00:00'),(13,1,'2015-07-20 03:00:00');
insert into T_HOOK(NUM_HOOK,NUM_TITLE,NAME) values (20,1,'hook1'),(21,1,'hook2');
left join T_STATS on T_TITLE.NUM_TITLE=T_STATS.NUM_TITLE
left join T_HOOK on T_TITLE.NUM_TITLE=T_HOOK.NUM_TITLE
This works but if I have 2 entries in HOOK and 4 entries in STATS, I get 8 lines total, with the cartesian product of HOOKxSTATS. I would like only 4 lines, with each information only once. Is this possible?