CODEWITHSUNDEEP
mysqlselectdistinct
user1033406
user1033406

Reputation: 133

SELECT DISTINCT values - multiple rows of same ID - multiple conditions

this is my starting table:

User_ID | FIELD_KEY    | VALUE
11      |  name        |  John
11      |  test1_score |  9
11      |  test2_score |  6
11      |  test3_score |  8
11      |  test5_score |  3
27      |  name        |  Peter
27      |  test1_score |  7
27      |  test3_score |  3
28      |  name        |  Nick
28      |  test1_score |  6
28      |  test2_score |  5
33      |  name        |  Felix
33      |  test1_score |  7
33      |  test2_score |  3

How can I select the unique User_IDs of the following

Conditions:

Seems quite a challenge...

Is it even possible with ONE query?

In this example result should be two users with User_ID: #11 and #33. That’s because even though user #28 also has both test1_score and test2_score entries, only for user #11 and #33 test1_score >= 7.

Ideally I’d get a result like this:

User_ID   |   NAME         | TEST1_SCORE  |  TEST2_SCORE    |
33        |     Felix      |    7         |  3              |
11        |     John       |    9         |  6              |

Any help is very much appreciated.

Thanks!

Upvotes: 0

Views: 2148

Answers (2)

yate
yate

Reputation: 804

Here's what I would do

select distinct t.id, name, test1_score, test2_score from t
inner join (select id, value test1_score from t where field_key = 'test1_score' and value >= 7) t1 on (t1.id = t.id)
inner join (select id, value test2_score from t where field_key = 'test2_score' and value is not null) t2 on (t2.id = t.id)
inner join (select id, value name from t where field_key = 'name') t3 on (t3.id = t.id)
order by test1_score;

sqlfiddle

Upvotes: 2

NealB
NealB

Reputation: 16928

Does this work for you?

SELECT A.USER_ID, 
       A.VALUE AS "NAME", 
       B.VALUE AS "TEST1_SCORE", 
       C.VALUE AS "TEST2_SCORE"
FROM MYTABLE A, MYTABLE B, MYTABLE C
WHERE A.USER_ID = B.USER_ID
  AND A.USER_ID = C.USER_ID
  AND A.FIELD_KEY = 'name'
  AND B.FIELD_KEY = 'test1_score'
  AND B.VALUE >= 7
  AND C.FIELD_KEY = 'test2_score'
ORDER BY 3 ASC
;

Upvotes: 1

Related Questions