CODEWITHSUNDEEP
sqlleft-join
Ben Fossen
Ben Fossen

Reputation: 1007

Joining two columns but keeping multiple instances in SQL

I am using SQL 2008 and am having a problem.

I have 3 different tables and here is a sample of my code.

SELECT DISTINCT Name Age
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID

This returns something like this:

Name Age
tom   12
ben   23
ian   12

I have another query

SELECT Name
FROM Table3

This returns this:

ian   12 
ian   12
ian   12

I want to verify that if a name and age are in name from the first query and is in the second query Table3.name it will return something like this:

ian   12 
ian   12
ian   12

I have been trying Join and Union on these two columns but so far have been only been able to get it to return. Any suggestions?

ian   12

Upvotes: 0

Views: 776

Answers (2)

roman
roman

Reputation: 117370

First you pronounce your problem - you want to get all records for which combination of Name and Age exists in recordset of distinct Name and Age from join. Then you use power of declarative language and a little of CTE to get your solution:

;with CTE as (
    select distinct Name, Age
    from Table1 as T1
        inner join Table2 as T2 on T2.ID = T1.ID
)
select *
from Table3 as T3
where exists (select * from CTE as C where C.Name = T3.Name and C.Age = T3.Age)

SQL FIDDLE EXAMPLE to fiddle with query

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

This might be able to be simplified (need to see your table structures and sample data), but given your queries, a subquery should work for you:

SELECT T3.Name, T3.Age
FROM Table3 T3
    JOIN (
        SELECT DISTINCT Name, Age
        FROM Table1
            LEFT JOIN Table2
                ON Table1.ID = Table2.ID
    ) T ON T3.Name = T.Name AND T3.Age = T.Age

Upvotes: 2

Related Questions