CODEWITHSUNDEEP
mysqlself-join
jdhar
jdhar

Reputation: 415

MySQL Compare Rows with empty entries

I've read a lot of the examples on self join, but they don't seem to cover the case where some fields are not in some rows.

For eg, I have a database with: 

testId, testItem, testResult

And the rows:

 1,test1,1
 1,test2,0
 1,test3,1
 2,test1,0
 2,test4,1
 2,test5,1

I would like the output:

testItem,a.testId,b.testId,a.testResult,b.testResult
test1,1,2,1,0
test2,1,NULL,0,NULL
test3,1,NULL,1,NULL
test4,NULL,2,NULL,1
test5,NULL,2,NULL,1

Essentially, I want to compare each testItem (test1->test5) from two different testIds (1 and 2) and compare their testResult values, factoring in testIds that may not have the same test Items.

Upvotes: 1

Views: 104

Answers (3)

Raghvendra Parashar
Raghvendra Parashar

Reputation: 4053

SQLFiddle Demo

select testItem, 
group_concat(IFNULL(testId,'null') separator ', ') testIds,
group_concat(IFNULL(testResult, 'null') separator ', ') testResults
from table_name group by testItem;

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

Given your exact requirement, you can try this:

select testItem
     , max(case when testID = 1 then testID else null end) as testID1
     , max(case when testID = 2 then testID else null end) as testID2
     , max(case when testID = 1 then testResult else null end) as testResult1
     , max(case when testID = 2 then testResult else null end) as testResult2
from mytable
where testID in (1,2)
group by testItem

This makes a lot of assumptions about your data, so take it with a grain of salt.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108380

It looks like you want a FULL OUTER JOIN, which is not supported in MySQL. You can emulate this with a UNION of two queries: a LEFT JOIN query and RIGHT JOIN which throws out matching rows.

Something like this will return the specified resultset:

SELECT a.testItem
     , a.testId      AS `a.testId`
     , b.testId      AS `b.testId`
     , a.testResult  AS `a.testResult`
     , b.testResult  AS `b.testResult`   
  FROM mytable a
  LEFT
  JOIN mytable b
    ON b.testItem = a.testItem
       AND b.testId = 2
 WHERE a.testId = 1
   AND a.testItem IN ('test1','test2','test3','test4','test5')
 UNION ALL
SELECT d.testItem
     , c.testId
     , d.testId
     , c.testResult
     , d.testResult
 FROM mytable d
 LEFT
 JOIN mytable c
   ON c.testItem = d.testItem
      AND c.testId = 1
WHERE d.testId = 2
  AND d.testItem IN ('test1','test2','test3','test4','test5')
  AND c.testId IS NULL
ORDER
   BY 1,2,4

(I included the predicates on testItem IN ('test1' thru 'test5') because you specified that as a requirement; those predicates could be removed if you want all values for testItem included.)

Upvotes: 0

Related Questions