MySQL Query That Can Pull the Data I am Seeking?

Question

On the project I am working on, I am stuck with the table structure from Hades. Two things to keep in mind:

1. I can't change the table structure right now. I'm stuck with it for the time being.
2. The queries are dynamically generated and not hard coded. So, while I am asking for a query that can pull this data, what I am really working toward is an algorithm that will generate the query I need.

Hopefully, I can explain the problem without making your eyes glaze over and your brain implode.

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

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

Then, there are multiple data tables along these lines:

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

Please note that reference_field2 in Table1 contains a reference to another instance. Field3 in Table2 is a bit more complicated. It contains a fieldID for Table 3.

What I need is a query that will get me a list as follows:

InstanceID    field1     field4     
1              John      Howard
2              Sally
3              Fred

The problem is, in the query I currently have, I do not get Fred because there is no entry in Table3 for fieldID 1 and InstanceID 6. So, the very best list I have been able to get thus far is

InstanceID    field1     field4     
1              John      Howard
2              Sally

In essence, if there is an entry in Table1 for Field 2, and there is not an entry in Table 3 that has the instanceID contained in field2 and the field ID contained in field3, I don't get the data from field1.

I have looked at joins till I'm blue in the face, and I can't see a way to handle the case when table3 has no entry.

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'

The two left joins should handle the case where there are no other rows...

Answer 2

It would help if you posted the query you have, because I think you have some mistakes in the table descriptions here, so it's not very clear how are the tables connected.

Anyway, you probably have an inner join in your query (normally written as just JOIN). Replace it with a left outer join (LEFT JOIN). It will not require the right table to contain the row and return NULL instead of the actual value.