CODEWITHSUNDEEP
sqlsql-servert-sqljoin
DrNoone
DrNoone

Reputation: 261

Logical AND between table elements in T-SQL

I have n tables all with the same fields: Username and Value. The same Username can have multiple registers on each table but the combination Username/Value is unique on each one.

I want to join the tables into a single one which contains all the users who appear on all the tables with all the different (Username/Value) pairs.

Example

Desired output

Table D: {(User1,Value1);(User1,Value2);(User1,Value4);(User1,Value5);(User3,Value4);(User3,Value5);(User3,Value8)}

Now I'm doing multiple joins (using perl) like this

SELECT * 
INTO $target_table 
FROM (SELECT *  
      FROM $table1 
      WHERE bname IN (SELECT DISTINCT bname FROM $table2) 
      UNION
      SELECT * 
      FROM $table2 
      WHERE bname IN (SELECT DISTINCT bname FROM $table1)
     ) UN

and then doing the same join between a third table and target_table and so on, but I think it should be a better way.

Any hints?

Upvotes: 1

Views: 128

Answers (2)

Thomas
Thomas

Reputation: 64655

With Combined As
  (
    Select 'A' As TableName, Username, Value
    From TableA
    Union All
    Select 'B', Username, Value
    From TableB
    Union All
    Select 'C', Username, Value
    From TableC
    )
Select C.Username, C.Value
From Combined As C
    Join (
          Select C1.Username
          From Combined As C1
          Group By C1.Username
          Having Count(Distinct C1.TableName) = 3
          ) As Z
      On Z.Username = C.Username
Group By C.Username, C.Value

SQL Fiddle version

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

You can use UNION for this:

SELECT username, value
FROM $table1
UNION
SELECT username, value
FROM $table2
...
SELECT username, value
FROM $tablex

This will return you distinct records. If you are interested in duplicates, use UNION ALL.

Given your edits, it appears you only want to return records if the user is in all the tables.

Breaking that down, you need to do a few things. First, combine all your records together again, but this time denote which table each are coming from. Then you need to know the count of tables each user is in. Finally you need to check that number against the overall number of tables.

Here's one way using a few CTEs:

WITH CTE AS (
  SELECT username, value, 1 AS tbl
  FROM t1
  UNION
  SELECT username, value, 2 AS tbl
  FROM t2
  UNION
  SELECT username, value, 3 AS tbl
  FROM t3
  ),
CTECnt AS (
  SELECT username, COUNT(DISTINCT tbl) tblCnt
  FROM CTE
  GROUP BY username
  ),
CTEMaxCnt AS (
  SELECT COUNT(DISTINCT tbl) MaxCnt
  FROM CTE
  )
SELECT C.username, C.value
FROM CTE C
  JOIN CTECnt C2 ON C.username = C2.username
  JOIN CTEMaxCnt C3 ON C2.tblCnt = C3.MaxCnt

Upvotes: 2

Related Questions