Self-Join ... new synthetic columns for values in existing column

Question

I did this once before but don't remember how ... I've struggled with it enough and am now looking for help.

I have a table with two columns: Uuid and ProcessId The ProcessId column currently has two values in it: ValueA and ValueB

I want to do a self join (full outer join?) such that with the following table:

        1234  ValueA
        1234  ValueB
        2345  ValueB
        3456  ValueA

I will have a result as follows:

        Uuid    ValueA      ValueB
        1234    OK          OK
        2345    Missing     OK
        3456    OK          Missing

Holger Brandt · Answer 1 · 2012-07-02T22:52:01.940

1

Try this:

SELECT Uuid, 
   CASE WHEN U.ValueA>0 THEN 'OK' ELSE 'Missing' END AS ValueA,
   CASE WHEN U.ValueB>0 THEN 'OK' ELSE 'Missing' END AS ValueB
FROM 
 (SELECT T.Uuid, 
     (SELECT COUNT(*) FROM MyTable AS M WHERE T.Uuid=M.UserID AND ValueField='ValueA') AS ValueA, 
     (SELECT COUNT(*) FROM MyTable AS M WHERE T.Uuid=M.UserID AND ValueField='ValueB') AS ValueB
  FROM (SELECT DISTINCT Uuid FROM MyTable) AS T
  GROUP BY Uuid) AS U
ORDER BY Uuid

edited Jul 02 '12 at 22:52

answered Jul 02 '12 at 22:32

Holger Brandt

4,324
1
20
35

You are welcome. Since you are new, it would be beneficial to mark the answer as "Answered". Thanks. – Holger Brandt Jul 03 '12 at 14:06
Holger - I know this isn't the best place to ask, but I went thru the FAQ and looked thru the meta postings ... how do I mark it as Answered? – Ron Alby Jul 03 '12 at 18:57
@RonAlby, click the checkmark next to the answer. (Note, you are only allowed one answer per question.) Thanks to asking. – Holger Brandt Jul 03 '12 at 19:19

score 0 · Answer 2 · answered Jul 03 '12 at 15:25

Depending on indexes, table size, and the optimizer, it might be fastest to use a pair of self-joins:

select TableU.Uuid, 
    if(count(TableA.ProcessId) > 0, 'OK', 'Missing') as ValueA, 
    if(count(TableB.ProcessId) > 0, 'OK', 'Missing') as ValueB 
from Table11301861 as TableU
left join Table11301861 as TableA on TableA.ProcessId = 'ValueA' and TableA.Uuid = TableU.Uuid
left join Table11301861 as TableB on TableB.ProcessId = 'ValueB' and TableB.Uuid = TableU.Uuid
group by TableU.Uuid

Self-Join ... new synthetic columns for values in existing column

2 Answers2