MySQL subquery to pull values by fieldname after join

Question

i'm trying to create a join in MySQL which will pull in values from another table based on a fieldname. I have these 2 tables:

TABLE_MAIN:

ID	EVENTID	FIELDNAME
1	1	FIELD1
2	1	FIELD2
3	1	FIELD3
4	2	FIELD1
5	3	FIELD2

TABLE_FIELDS:

EVENTID	FIELD1	FIELD2	FIELD3
1	Some value	text	value
2	foo	bar	text
3	differentValue	more	text

I'm trying to create something like below which i obviously cant do with a normal join:

ID	EVENTID	FIELDNAME	VALUE
1	1	FIELD1	Some value
2	1	FIELD2	text
3	1	FIELD3	value
4	2	FIELD1	foo
5	3	FIELD2	more

Ive started doing a simple join like this:

SELECT * FROM TABLE MAIN LEFT JOIN TABLE_FIELDS ON TABLE_MAIN.EVENTID = TABLE_FIELDS.EVENTID

This however pulls in the full table, how would I be able to only pull in the field thats specify in the fieldname? Thanks! Robert

Answer 1

You need case expressions as well as join:

select m.*,
       (case when m.fieldname = 'FIELD1' then f.field1
             when m.fieldname = 'FIELD2' then f.field2
             when m.fieldname = 'FIELD3' then f.field3
        end) as value
from table_main m left join
     table_fields f
     on m.eventid = f.eventid;

You should really fix your data model so you can use a join. If the second field had each value in a separate row, you could just use a regular join.