jDatabase request whith 2 times the same field under condition-CodePudding

I would like to request a table which looks like this:

Table1

record	element	value
62	56	637689
62	163	12/1990

... joined with another table:

Table2

user_id	record
64	62

expecting this result: 637689,"12/1990" based on user_id=64 (not implemented in my request as i am unable to write the correct JOIN syntax

i tried with this request:

 SELECT record,
   (CASE WHEN element = 163 THEN value END) AS numserie,
   (CASE WHEN element = 56 THEN value END) AS dateprod
   FROM Table1
   GROUP BY record
   ORDER BY record DESC;

, but in heidiSQL, i have this result

numserie	dateprod
637689	NULL

i tried on others "element" number, always NULL

i tried to swap the two CASE lines, the result swap either

What is wrong ?

CodePudding user response：

You need to aggregate the CASE expressions. Using the MAX function is one option:

SELECT
    record,
    MAX(CASE WHEN element = 163 THEN value END) AS numserie,
    MAX(CASE WHEN element = 56  THEN value END) AS dateprod
FROM Table1
GROUP BY record
ORDER BY record DESC;