Faranak Faranak - 18 days ago 5
SQL Question

costume order for multiple joins in sql

I am trying to write a query in sql that replicates following figure:

enter image description here

I want the result of first left join (order_defect & ncdef) to be left join with third table (filter) and again the result of these three left join with last one (nsdic).

each of these tables are hugeeee, so I'm trying to find most efficient way to do it because i have limited space and I get "out of memory" error...any suggestion for an efficient query?
if I do:

Select * from A
left join B
on a.id=B.id

left join C
on a.id=c.id


it's joining A and B first and then A and C...but I want the result of "A & B" to be join with "C".
basically my question is how to use a result of one join, to join with another table.
Thank you

Answer

It's dificult to help you without the tables design and fields|columns, keys, ...

But I'll considerate:

1 - Primary keys fields, and how to relation the tables

2 - How to add left joins with "filters", or how to reduce the number of results

3 - Evaluate if it'll be better to use Sub-querys

Plus: Try the query with TOP 100 <--- to make test.

And remember: sometimes it's imposible to optimizate querys because of the hardware limits, like the RAM, in those case you have to show the data in sections.