0

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
  • 2
    You should really start using the modern explicit JOIN operator instead of the ancient, outdated and fragile implicit joins in the WHERE clause (and a table alias have that is the same as the table's name doesn't seem very useful to me) – a_horse_with_no_name Oct 18 '16 at 13:23
  • 1
    Stop writing queries in ANSI-89 standard and learn "new" SQL ANSI-92 standard which is much easier to read and also will be easier for you to answer your own question. – Vladimir Oselsky Oct 18 '16 at 13:27
  • I will follow your recommendation on ANSI-92 standard. – John Oct 18 '16 at 13:50
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.