Cross or Inner Join two tables that have one non-unique column in common-CodePudding

I have two tables; the first shows all (unique) persons. It contains a column showing the type of house each person would like to have. The second table shows all unique ads. Each row also contains the type of house in the ad. So HouseType is not unique in both tables.

I'd like to have a SQL statement that results in all possible combinations as long as HouseType is the same. I suspect I need some kind of cross join statement. Hope you can help.

Table 1:

PersonID    Name    HouseType
-------------------------------
P1          Jane    Appartment
P2          Jack    Mansion
P3          Jo      Appartment
P4          Jill    Studio

Table 2:

AdID    HouseType
-------------------
A1      Mansion
A2      Appartment
A3      Mansion
A4      Appartment
A5      Studio
A6      Studio
A7      Appartment

Desired output table from SQL statement:

AdID    HouseType   PersonID    Name
-------------------------------------
A1      Mansion     P2          Jack
A2      Appartment  P1          Jane
A2      Appartment  P3          Jo
A3      Mansion     P2          Jack
A4      Appartment  P1          Jane
A4      Appartment  P3          Jo
A5      Studio      P4          Jill
A6      Studio      P4          Jill
A7      Appartment  P1          Jane
A7      Appartment  P3          Jo

CodePudding user response：

Cross JOIN will used only when you need to repeat all records of Table2 per every single record of table2 but in your case things are different.

You should use INNER JOIN

SELECT t2.AId, t2.HouseType, t1.PersonId, t1.Name
FROM Table1 t1
  Inner JOIN Table2  t2 ON t1.HouseType = t2.HouseType

CodePudding user response：

I'd like to add one additional complexity to my question: Each person now has a value in column Garden. Persons that have value 1 need a garden. Persons that have value 0 don't mind, so we want to see both ads with and without gardens for them. So, person Jack needs a garden and we don't want to see ad 'A3'.

Table 1:

PersonID    Name    HouseType    Garden
----------------------------------------------
P1          Jane    Appartment   0
P2          Jack    Mansion      1
P3          Jo      Appartment   0
P4          Jill    Studio       0

Table 2:

AdID    HouseType    Garden
-----------------------------------
A1      Mansion      1
A2      Appartment   0
A3      Mansion      0
A4      Appartment   1
A5      Studio       0
A6      Studio       1
A7      Appartment   1

Desired output table from SQL statement:

AdID    HouseType   Garden   PersonID    Name
----------------------------------------------
A1      Mansion     1        P2          Jack
A2      Appartment  0        P1          Jane
A2      Appartment  0        P3          Jo
A4      Appartment  1        P1          Jane
A4      Appartment  1        P3          Jo
A5      Studio      0        P4          Jill
A6      Studio      1        P4          Jill
A7      Appartment  1        P1          Jane
A7      Appartment  1        P3          Jo