Join with more records in result

join;

I have this query:

SELECT table1.foo, table2.bar, table3.abc, table4.xyz
  FROM table1 table1, table2 table2, table3 table3, table4 table4
 WHERE table1.id = table2.id 
  AND table1.id2 = table3.id2 
  AND table1.id3 = table4.id3 
  AND (table1.foo BETWEEN 10 AND 30)

I'd like to get additional rows in results, with data in table1 that doesn't have matching records in table2 and table3, but still only those that match data in table4.
How can I write this?
I think I need two left joins and keep the third normal join.

Tables:

TABLE1:
 id (references TABLE2),
 id2 (references TABLE3),
 id3 (references TABLE4),
 foo

TABLE2
 id,
 bar

TABLE3
 id,
 abc

TABLE4
 id,
 xyz

Best Answer

You want query looking like this one:

SELECT foo.table1, bar.table2, table3.abc, table4.xyz 
  FROM table1 
 LEFT OUTER JOIN table2
  ON table1.id2 = table2.id
 LEFT OUTER JOIN table3
  ON table1.id3 = table3.id
 JOIN table4
  ON table1.id4 = table4.id
 WHERE table1.foo BETWEEN 10 AND 30

It's in SQL JOIN syntax, so You might want to check some tutorial or course, like one from CodeAcademy. Another great source helpful with learning about joins is Jeff Atwood's visual explanation of sql joins.

Just like @a_horse_with_no_name and @Vladimir_Oselsky commented, You should switch from old ANSI-89 SQL syntax to current ANSI-92, which allows for easier to write (and read) queries with more flexible JOIN syntax.

Related Question