Link one table to another through two columns in SQL

Question

So I have two Tables. The first are Names and their qualifications:

Users:

Name    Qualification
---------------------
User1   QualA  
User1   QualB  
User1   QualC  
User2   QualA  
User2   QualD  

Then a Second table that links two qualifications from the first to another attribute:

Attributes:

Attribute   Qual1  Qual2
------------------------
Attr1       QualA  QualC    
Attr2       QualB  QualC    
Attr3       QualA  QualD    
Attr4       QualB  QualD    

Now I want to query the data so I get something like this in return:

User     Attribute
------------------
User1    Attr1   
User1    Attr2  
User2    Attr3  

So if the Name has the two qualifications required for the Attribute, they can be associated together.

Answer 1

I would use this:

select
  name, attribute
from
  users inner join attributes
  on users.qualification in (attributes.qual1, attributes.qual2)
group by attribute, name
having count(*)=2

I am trying to join each qualification for each user with attributes tables, based on any of the qualifications needed for the attribute. Then I'm grouping by attribute and name and counting the rows.

If a combination of username and attribute has 2 rows, it means that the user has two qualifications for the attribute, and we have to show it.

Answer 2

I had to produce a very similar query, however in my scenario Individuals or Users could be issued with the same Qualification more than once. Hence I had to come with something a bit more complicated than fthiella's solution.

The query I came up with is as follows:

SELECT 
    j2.name,
    Qualification1,
    Qualification2,
    t3.Attribute
FROM
    (SELECT 
        t1.name,
        t1.qualification AS Qualification1,
        J1.qualification AS Qualification2,
        Rank() over (Partition BY t1.name, t1.qualification ORDER BY t1.qualification, J1.qualification) AS rank1,
        Rank() over (Partition BY t1.name, J1.qualification ORDER BY J1.qualification, t1.qualification) AS rank2
    FROM 
        Users t1
    LEFT JOIN
        (SELECT 
            t2.name,
            t2.qualification
        FROM 
            Users t2) J1
    ON 
        t1.name = J1.name) J2
LEFT JOIN 
    Attributes t3
ON 
    t3.Qual1 = Qualification1 
    AND t3.Qual2 = Qualification2
WHERE 
    rank2 <= rank1 
    AND t3.Attribute IS NOT NULL

Basically I'm joining the table Users upon itself, listing all qualification combinations for each user without duplications. This then allows us to do a simple join of the Attributes table.

Of course if your not working with Sql server 2005 or later than the Rank function won't be available to you.