I have been trying for a few days to generate a query for said Entity-Attribute-Value database and I have gotten a bit rusty with sql, so even after lots of research I can't manage to get the query to do exactly as I want.
The important parts of the table look like this:
ID | concept | modifier 1 | abc:10 | @ 2 | abc:22 | @ 2 | abc:22 | t:f 2 | abc:78 | @ 2 | abc:78 | t:x 3 | kfg:12 | @ 4 | aqx:23 | @ 5 | abc:49 | @ 5 | abc:49 | t:f 5 | abc:49 | t:g
I want a table like this:
ID | concept | mod_f | mod_other 1 | abc:10 | null | null 2 | abc:22 | t:f | null 2 | abc:78 | null | t:x 5 | abc:49 | t:f | t:g
My attempts got me already to this result:
ID | concept | mod_f | mod_other 1 | abc:10 | null | null 2 | abc:22 | t:f | null 2 | abc:22 | null | null 2 | abc:78 | null | t:x 2 | abc:78 | null | null 5 | abc:49 | t:f | null 5 | abc:49 | t:f | t:g 5 | abc:49 | null | t:g 5 | abc:49 | null | null
Here is the code that produced my result:
SELECT t.ID, t.concept, t.modifier,
case when t.concept LIKE 'abc:%' and t.modifier = '@' then t.concept end AS "concept",
case when t2.modifier = 't:f' then t2.modifier end AS "mod_f",
case when t3.modifier LIKE 't:%' then t3.modifier end AS "mod_other"
FROM table as t
LEFT JOIN table as t2 ON t.ID = t2.ID and t.concept = t2.concept
LEFT JOIN table as t3 ON t2.ID = t3.ID and t2.concept = t3.concept
WHERE t.concept LIKE 'abc:%' and t.modifier = '@' and
(t2.modifier LIKE 't:f' or t2.modifier ='@')and
(t3.modifier = 't:g' or t3.modifier = 't:x' or t3.modifier ='@')
ORDER by t.ID asc;
The fix looks kind of easy, but whatever I have tried reduces the results to way less than what I want (deletes too many rows basically) and at this point I don't know what I need to search for anymore.
Also the "case" part in the query looks kind of a dirty solution, that I found somewhere and used because it works (makes the "@" into "null"), so if you have a any suggestion to make the query more clean, feel free to reply.
Okay, I found the answer and it was easier than I thought.
GROUP by is the answer. I thought this won't work for text, but you can actually aggregate the text-case select statements with max like this:
SELECT t.ID, t.concept, t.modifier,
max(case when t.concept LIKE 'abc:%' and t.modifier = '@' then t.concept end) AS "concept",
max(case when t2.modifier = 't:f' then t2.modifier end) AS "mod_f",
max(case when t3.modifier LIKE 't:%' then t3.modifier end) AS "mod_other"
FROM table as t
LEFT JOIN table as t2 ON t.ID = t2.ID and t.concept = t2.concept
LEFT JOIN table as t3 ON t2.ID = t3.ID and t2.concept = t3.concept
WHERE t.concept LIKE 'abc:%' and t.modifier = '@' and
(t2.modifier LIKE 't:f' or t2.modifier ='@')and
(t3.modifier = 't:g' or t3.modifier = 't:x' or t3.modifier ='@')
GROUP by t.ID, t.concept, t.modifier
ORDER by t.ID asc;
This gets me exactly the table I was looking for. But this only works because mod_other can contain only 1 code per concept.