CODEWITHSUNDEEP
sqlsqlite
Ian Clay
Ian Clay

Reputation: 592

SQLite select and group matches across columns

I have an SQLite table called match that has two columns: column1 and column2 that contain integer values:

column1   column2
------------------

5          6
6          8
8          9
90         91
1          20
10         20

I want to match duplicate numbers found in either columns and join them, including the matches second value, so that my search result returns would be:

5, 6, 8, 9 
1, 20, 10

(notice that 90 and 91 have no matches and therefore are not included).

My 'guess' at making this is:

SELECT column1, column2 
FROM match 
WHERE column2 
IN (SELECT column1 
    FROM match 
    GROUP BY column1 HAVING (COUNT(column1) > 0)) 
    UNION 
    SELECT column1, column2 
    FROM match 
    WHERE column1 
    IN (SELECT column2 
        FROM match 
        GROUP BY column1 HAVING (COUNT(column2) > 0)) 
        UNION 
        SELECT column1, column2 
        FROM match 
        WHERE column1 
        IN (SELECT column1
            FROM match 
            GROUP BY column1 HAVING (COUNT(column1) > 1)) 
            UNION 
            SELECT column1, column2 
            FROM match 
            WHERE column2 
            IN (SELECT column2 
                FROM match 
                GROUP BY column2 HAVING (COUNT(column2) > 1))

and the result is almost what I need:

5 6
6 8
8 9
1 20
10 20

But what I really need is to have the result grouped somehow. For example:

(5, 6, 8, 9) (1, 10, 20)

Is this possible? And is my SQL attempt over-complicated?

Upvotes: 1

Views: 524

Answers (1)

invertedSpear
invertedSpear

Reputation: 11054

I think this is what you want: http://sqlfiddle.com/#!7/05747/9

SELECT column1 as newColumn
FROM match WHERE column1 in (

SELECT myColumn
FROM(

SELECT count(*) as cnt, myColumn
FROM (
SELECT column1 as myColumn
FROM match
UNION ALL
SELECT column2 as myColumn
FROM match
 ) x
GROUP BY myColumn
HAVING cnt > 1
  ) y
) OR column2 in (
SELECT myColumn
FROM(

SELECT count(*) as cnt, myColumn
FROM (
SELECT column1 as myColumn
FROM match
UNION ALL
SELECT column2 as myColumn
FROM match
 ) x
GROUP BY myColumn
HAVING cnt > 1
  ) y
)
UNION
SELECT column2 as newColumn
FROM match WHERE column1 in (

SELECT myColumn
FROM(

SELECT count(*) as cnt, myColumn
FROM (
SELECT column1 as myColumn
FROM match
UNION ALL
SELECT column2 as myColumn
FROM match
 ) x
GROUP BY myColumn
HAVING cnt > 1
  ) y
) OR column2 in (
SELECT myColumn
FROM(

SELECT count(*) as cnt, myColumn
FROM (
SELECT column1 as myColumn
FROM match
UNION ALL
SELECT column2 as myColumn
FROM match
 ) x
GROUP BY myColumn
HAVING cnt > 1
  ) y
)

Upvotes: 1

Related Questions