MySQL Query that can pull the data I'm looking for?

Question

In the project I'm working on, I stick to the table structure from Hades. Two things to remember:

• I can’t change the table structure right now. I am stuck with him now.
• Requests are dynamically generated, not hardcoded. Therefore, while I ask for a request that can pull this data out, I am really working on this algorithm, which will generate the request I need.

I hope I can explain the problem without causing your eyes to cool, and your brain explodes.

We have an instance table that looks (simplified) in these lines:

Instances
InstanceID active
1 Y
2 Y
3 y
4 N
5 y
6 y

Then these rows contain several data tables:

Table1

InstanceID    field1   reference_field2
1             John      5
2             Sally        NULL
3             Fred          6
4             Joe          NULL


Table2
InstanceID    field3 
5              1
6              1


Table3
InstanceID    fieldID    field4
5               1        Howard
5               2        James
6               2        Betty

, reference_field2 1 . 3 2 . field 3.

, :

InstanceID    field1     field4     
1              John      Howard
2              Sally
3              Fred

, , , Fred, 3 fieldID 1 InstanceID 6. , , , -

InstanceID    field1     field4     
1              John      Howard
2              Sally

, 1 2 , 3 , instanceID, 2, , 3, 1.

, , , table3 .

Answer 1

LEFT JOIN...

SELECT a.InstanceID, b.field1, d.field4
FROM instances AS a 
    JOIN Table1 AS b ON a.InstanceID = b.InstanceID
    LEFT JOIN Table2 AS c ON b.reference_field2 = c.InstanceID
    LEFT JOIN Table3 AS d ON (c.InstanceID = d.InstanceID AND c.field3 = d.fieldId)
WHERE a.active = 'Y'

, ...

Answer 2

, , , , , , .

, , ( JOIN). (LEFT JOIN). NULL .