When trying the first of your suggestions, the result I got was this: +-----------+-------------+-------+-------+ | object_id | value_table | value | value | +-----------+-------------+-------+-------+ | 1 | 1 | Hallo | 123 | | 1 | 2 | Hallo | 123 | +-----------+-------------+-------+-------+
The problem here is that in both rows of the result, both the string_value and the int_value are present. I get this result both in SQLite and in MySQL.
Any more ideas on how to get the result I want from SQLite? Or is this something that can't be done?
You never said what happened when you tried my *second* suggestion, and ignored value_table entirely. That's what I *really* wanted you to do ...
Actually, just try this instead:
SELECT
properties.object_id,
string_values.value,
int_values.value
FROM properties
LEFT OUTER JOIN string_values ON properties.property_id = string_values.property_id
LEFT OUTER JOIN int_values ON properties.property_id = int_values.property_id
;
This suggestion cuts to the chase and shows what you really should be doing.
FYI, this may mean the same thing, but proper expression grammar has dependent and independent values in an equivalence test always on a certain side of the '='. So you should write like this:
SELECT
properties.object_id,
string_values.value,
int_values.value
FROM properties
LEFT OUTER JOIN string_values ON string_values.property_id = properties.property_id
LEFT OUTER JOIN int_values ON int_values.property_id = properties.property_id
;
You might want to see if that last one gives different results.
-- Darren Duncan
