MySQL Query That Can Pull the Data I am Seeking?
- by Amy
On the project I am working on, I am stuck with the table structure from Hades. Two things to keep in mind:
I can't change the table structure right now. I'm stuck with it for the time being.
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.
