CODEWITHSUNDEEP
mysqlsqlmariadb
Sandeep
Sandeep

Reputation: 1401

Same Join Constraints on Multiple Columns

We have a situation that I have been able to recreate with the following simple example. I have the following two sample tables:

CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  priContactId INT,
  secContactId INT,
  blahBlah VARCHAR(32),

  PRIMARY KEY(id)  
);

and

CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contactID INT,
  contactName VARCHAR(32),

  PRIMARY KEY(id)  
);

I populate them as follows:

INSERT INTO contact_info(priContactId, secContactId, blahBlah) VALUES(1, 3, "Team A"), (4, 2, "Team B");
INSERT INTO name_lookup(contactID, contactName) VALUES(1, "John Doe"), (2, "Mary Smith"), (3, "Jose Garcia"), (4, "Larry Brown");

Obviously, the contents of the tables are as follows:

+----+--------------+--------------+----------+
| id | priContactId | secContactId | blahBlah |
+----+--------------+--------------+----------+
|  1 |            1 |            3 | Team A   |
|  2 |            4 |            2 | Team B   |
+----+--------------+--------------+----------+

+----+-----------+-------------+
| id | contactID | contactName |
+----+-----------+-------------+
|  1 |         1 | John Doe    |
|  2 |         2 | Mary Smith  |
|  3 |         3 | Jose Garcia |
|  4 |         4 | Larry Brown |
+----+-----------+-------------+

We would like to perform a JOIN operation so that we get output like this:

+-------------+-------------+--------+
| John Doe    | Jose Garcia | Team A |
+-------------+-------------+--------+
| Larry Brown | Mary Smith  | Team B |
+-------------+-------------+--------+

The join constraints for both the priContactId and secContactId columns are the same and I am having tough time figuring out what the JOIN query should look like.

FYI, we are using MySQL version 5.6.49.

Upvotes: 0

Views: 112

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

This would normally be handled with two joins. You don't have not null constraints on the two contacts, so I would suggest outer joins:

select nl_pri.contactName as PrimaryName, nl_sec.contactName as SecondaryName,
       ci.blahblah
from contact_info ci left join
     name_lookup nl_pri
     on ci.priContactId = nl_pri.contactId left join
     name_lookup nl_sec
     on c.secContactId = nl_sec.contactId;

Notes:

  • The left join keeps all contacts, even those that don't have both primary and secondary.
  • Table aliases make the query easier to write and read. The table aliases should be meaningful.
  • You should define the foreign key relationships, so it is clear that the contact ids refer to the name_lookup table.

Upvotes: 1

Akina
Akina

Reputation: 42739

Two separate columns needs 2 separate table copies join.

SELECT t1.contactName name1, t2.contactName name1, t3.blahBlah team
FROM name_lookup t1
JOIN name_lookup t2
JOIN contact_info t3 ON t1.contactID = t3.priContactId
                    AND t2.contactID = t3.secContactId

Upvotes: 1

Related Questions