CODEWITHSUNDEEP
sqlsql-server
MMMagic
MMMagic

Reputation: 182

Compare multiple Rows Based on another table

Lets say I have following tables:

Table1

 ID Number
 1  2
 2  34
 3  1                <---- Input (ID = 3) ==> (Number = 1)
 4  6
 5  5
*6* 7                <---- Want to find (Number = 6) because match in Table2
 7  22

and Table2

Number  Code    Att1    Att2    Att3
  1      1        1                           <-----|
  1      2        1      2                    <-----|
  6      2        f      2                          |
  6      3        4      3       2                  |
  2      4        6                                 |---Match
  22     5        2      2       2                  |
  5      2        h      3       b                  |
  7      1        1                           <-----|
  7      2        1      2                    <-----|
  7      h        5      r

So here is my Problem: I want the IDs from Table1 that have all Code and Attributes from Table2 that a given (variable) input ID has. At the end I want to create a stored procedure/function that gives me all IDs meeting that condition.

As an Example: Input-ID: 3. Would return ID 6 because Number 7 (mapped from ID 6 in Table1) has the rows Number 1 (mapped from ID 3 in Table1) has. It has more but that doesn't matter, its just important it has all rows the input one has.

(I can't find a solution to comparing a set of rows to another set of rows that is not known before.)

Thanks for any help!

Edit: To make it more understandible, here what I want in words step-by-step.

  1. Map input ID to Number in Table1

  2. Get All Rows from Table2 having Number from Step 1

  3. Get all Number that have the same (can have more) Rows as from Step 2

  4. Get IDs for that Numbers (and return them)

Upvotes: 0

Views: 120

Answers (3)

Andrey Davydenko
Andrey Davydenko

Reputation: 350

 select t11.ID as Id_To_Find,t12.ID as Id_Found 
    from Table1 t11
    join (
            select t21.Number as Found,t22.Number as ToFind from Table2 t21
            left join Table2 t22 on t21.Code = t22.Code 
                    and t21.Att1 = t22.Att1 
                    and t21.Att2 = t22.Att2 
                    and t21.Att3 = t22.Att3 
                    and t21.Number <> t22.Number
                    group by t21.Number,t22.Number
                    having COUNT(*) = (select COUNT(*) from Table2 where Number = t22.Number)) 
            as FindMatches 
            on t11.Number = FindMatches.ToFind              
    join Table1 t12  on t12.Number = FindMatches.Found

Upvotes: 1

Adam Martin
Adam Martin

Reputation: 1218

Try something like this. Haven't tested it, but basically you inner join on all of the attributes that need to match. The HAVING clause is a crude check to make sure that it matched all the rows. Edit: Forgot to add the input ID WHERE clause.

SELECT t1b.ID FROM 
Table1 t1a 
INNER JOIN Table2 t2a ON t1a.Number = t2a.Number
INNER JOIN Table2 t2b ON t2a.Number <> t2b.Number AND t2a.Code = t2b.CODE AND t2a.Att1 = t2b.Att1 AND t2a.Att2 = t2b.Att2 AND t2a.Att3 = t2b.Att3
INNER JOIN Table1 t1b ON t1b.Number = t2b.Number
WHERE t1a.ID = 3
GROUP BY t1b.ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Table1 WHERE ID = t1a.ID)

Upvotes: 2

tobypls
tobypls

Reputation: 849

Kind of hard to understand what you're trying to acheive. As i understood from your example, you want to match the Number for the input ID in Table1 with any column (correct?) in Table2.

With input ID=3, the SELECT will return Number=7. In the IN (...)-condition, you can specify whichever columns in Table2 you want to match to Table1.Number.

DECLARE @Input INT = 3 -- Your input

SELECT DISTINCT t1.Number
FROM Table1 t
INNER JOIN Table2 t2 ON t.Number IN (t2.Number, t2.Code, t2.Att1, t2.Att2, t2.Att3)
INNER JOIN Table1 t1 ON t2.Number = t1.Number AND t.ID <> t1.ID
WHERE t.ID = @Input

Upvotes: 0

Related Questions