0

There are 3 (will be up to 6 in the future) tables with the same columns.

I need to unify them, i.e. union on same columns. In addition to this - rows shall not be unique, based on 2 column combination! There are a couple of examples on the net, but all of them show how to exclude unique column values based on WHERE for one column. In my case there are 2 columns (Col1 and Col2 combination).

Here are the schematics:

enter image description here

and

enter image description here

And here is how I imagined final query (for 3-tables) would look like:

SELECT
*
FROM
(
SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3
)
GROUP BY
Col1, Col2
HAVING
COUNT (*) > 1

What would be a correct way?

P.S. FYI single-column solutions

Multiple NOT distinct

How to select non "unique" rows

How to Select Every Row Where Column Value is NOT Distinct

EDIT:

I have used the code from accepted answer and added additional search criteria:

  ON (SOUNDEX(Merged.[name_t1]) = SOUNDEX(Multiples.[name_t1]) OR Merged.[name_t1] LIKE '%' + Multiples.[name_t1] + '%' OR Multiples.[name_t1] LIKE '%' + Merged.[name_t1] + '%')
  AND (SOUNDEX(Merged.[name_t2]) = SOUNDEX(Multiples.[name_t2]) OR Merged.[name_t2] LIKE '%' + Multiples.[name_t2] + '%' OR Multiples.[name_t2] LIKE '%' + Merged.[name_t2] + '%')

search col1 and col2:

-by SOUNDEX

-by col1 like (col1 from other table)

-by (col1 from other table) like col1

Community
  • 1
  • 1
Alex
  • 4,607
  • 9
  • 61
  • 99
  • What variety of SQL? MS-SQL, PostgreSQL, MySQL, etc? If you have the power of Common Table Expressions available to you, I daresay I could rustle up something..... – AjV Jsy May 12 '14 at 22:46
  • @AjVJsy That's a sqllite db – Alex May 13 '14 at 05:58
  • 1
    That's good, that allows a technique I get on with, according to http://www.sqlite.org/lang_with.html - you can use a double CTE approach. UNION ALL your tables together into the first CTE, have a second one GROUPED BY the Cols1&2 HAVING count>1, then in the main SELECT you can join between the two to return all rows from the first CTE that are in the 2nd. I can provide a skeleton outline in a few hours time if you need more.. – AjV Jsy May 13 '14 at 07:40
  • @AjVJsy looking forward to it! The main idea is to union 3 or more tables together with the non-unique col1 and col2. After work I will try to dig more information on solution that you have proposed! thanks – Alex May 13 '14 at 08:15

1 Answers1

1

Here's the basics of a CTE-based approach :

With Merged AS
(  -- CTE 1 : All Data in one table
 SELECT * FROM table1
  UNION ALL
 SELECT * FROM table2
  UNION ALL
 SELECT * FROM table3
) 
, Multiples AS
(  -- CTE 2 : Group by the fields in common
 SELECT   Col1, Col2
 FROM Merged
 GROUP BY Col1, Col2
 HAVING Count(*)>1 -- only want Groups of 2 or more
)
SELECT
  Merged.*
FROM Merged INNER JOIN Multiples
  -- Only return rows from Merged if they're in Multiples
   ON Merged.[Col1]=Multiples.[Col1]
  AND Merged.[Col2]=Multiples.[Col2]

Something like that works with my own example MS-SQL data, and it looks like SQLite syntax is the same. HTH!

AjV Jsy
  • 5,799
  • 4
  • 34
  • 30
  • thank you! i will prepare the test data today in the evening a do some tests – Alex May 14 '14 at 09:59
  • works like a charm! Except - I had to add additional conditions for select.. see the post update – Alex May 14 '14 at 19:51