Link one table to another through two columns in SQL

join, sql

Solution

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.

Problem

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.

Original source