CODEWITHSUNDEEP
mysql
Jtello
Jtello

Reputation: 802

Query to find differences in same table

So I am running into a problem with getting the right data from my query.

I have the following table (Person):

Name  |       xpath        |   value
Derek |  /body/torso/arm   |   left
Derek |  /body/torso/arm   |   right
Derek |  /body/torso/neck  |   Head
Ron   |  /body/torso/neck  |   Head
Ron   |  /body/torso/arm   |   left

So basically the query results i was trying to get were to show the differences (whats missing between the two people).

results:
Name1 |       xpath1       |   value1   | Name2  |   xpath2         |   value2
Derek |  /body/torso/arm   |   right    | Ron    |    NULL          |   NULL

I would even be fine with getting the following back as well

results:
Name1 |       xpath1       |   value1   | Name2  |   xpath2         |   value2
Derek |  /body/torso/arm   |   right    | Ron    |    NULL          |   NULL
Derek |  /body/torso/arm   |   left     | Ron    | /body/torso/arm  |   left  
Derek |  /body/torso/neck  |   Head     | Ron    | /body/torso/neck |   Head

The query i was using was:

SELECT P.Name , P.xpath, P.value, P1.Name, P2.xpath, P3.value
FROM Person as P
LEFT OUTER JOIN
    Person as P2 ON P.xpath = P2.xpath
WHERE
         P.Name = "Derek"
    AND  P2.Name = "Ron"

The results I keep getting no matter what i try is basically the overlapping data but never what i am actually looking for, seems I am missing something simple or just plainly doing it wrong. Any suggestions? Ultimately i am going to run this in a SPROC but it would be nice to be able to handle multiple names not just 2, but if i had, Derek,Ron,John,Dawn, etc..

Upvotes: 1

Views: 116

Answers (2)

Bob TN
Bob TN

Reputation: 1

Try using the EXCEPT union:

WITH tbl (name, xpath, value) as
    (values
        ('Derek','/body/torso/arm','left'),
        ('Derek','/body/torso/arm','right'),
        ('Derek','/body/torso/neck','Head'),
        ('Ron','/body/torso/neck','Head'),
        ('Ron','/body/torso/arm','left')
    )
SELECT xpath, value 
FROM tbl 
WHERE name = 'Derek'
    EXCEPT
        SELECT xpath, value 
        FROM tbl 
        WHERE name = 'Ron'

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

Give this a shot:

SELECT    a.Name  AS Name1,
          a.xpath AS xpath1,
          a.value AS value1,
          'Ron'   AS Name2,
          b.xpath AS xpath2,
          b.value AS value2
FROM      Person a
LEFT JOIN Person b ON b.Name = 'Ron'
                  AND a.xpath = b.xpath 
                  AND a.value = b.value
WHERE     a.Name = 'Derek' AND
          b.Name IS NULL

SQLFiddle Demo

If you wanted to compare Derek to multiple names, you can do a CROSS JOIN of all names to compare to (so that the names can appear in the result set), and LEFT JOIN the same table using xpath, value, and also the names specified in the CROSS JOIN:

SELECT     a.Name  AS Name1,
           a.xpath AS xpath1,
           a.value AS value1,
           b.Name  AS Name2,
           c.xpath AS xpath2,
           c.value AS value2
FROM       Person a
CROSS JOIN (
           SELECT 'Ron' AS Name UNION ALL
           SELECT 'John'        UNION ALL
           SELECT 'Charles'     UNION ALL
           SELECT 'Aaron'
           ) b
LEFT JOIN  Person c ON b.Name  = c.Name 
                   AND a.xpath = c.xpath
                   AND a.value = c.value
WHERE      a.Name = 'Derek' AND
           c.Name IS NULL

SQLFiddle Demo

And if you wanted to have multiple names on the left side of the comparison (not just "Derek"), just change

a.Name = 'Derek'

to

a.Name <> b.Name

in the above query.

SQLFiddle Demo

EDIT: Just to take it even further:

Compare all names to all names:

SELECT     a.Name  AS Name1,
           a.xpath AS xpath1,
           a.value AS value1,
           b.Name  AS Name2,
           c.xpath AS xpath2,
           c.value AS value2
FROM       Person a
CROSS JOIN (SELECT DISTINCT Name FROM Person) b
LEFT JOIN  Person c ON b.Name  = c.Name 
                   AND a.xpath = c.xpath
                   AND a.value = c.value
WHERE      a.Name <> b.Name AND 
           c.Name IS NULL

Upvotes: 2

Related Questions