CODEWITHSUNDEEP
mysqlsqljoin
CWZY
CWZY

Reputation: 15

MYSQL: How to JOIN two tables on the same query referencing the same table twice

I have two tables. I'm trying to JOIN the sample two tables below with table 1 referencing Table 2 twice. For example if I look at Table 1: Group 2 and Members 7, it should look up the ID in Table 2 and give me an output of:

Group Members Name   Name
  2      7     Blue   Dog

Table 1

Group  Members
 2       7
 3       8
 5       10

Table 2

 ID     Name
 1      Green
 2      Blue
 3      Yellow
 4      Orange
 5      Red
 6      Elephant
 7      Dog
 8      Cat
 9      Chicken
 10     Mouse

Any Advice? Thanks

Upvotes: 0

Views: 354

Answers (3)

kjh
kjh

Reputation: 15

Hard to tell exactly what you need from that description but aliasing the tables may be what you need. It works like this:

SELECT t1.x, t2_1.y, t2_2.z
FROM table1 AS t1
JOIN table2 AS t2_1 ON t1.whatever = t2_1.whatever
JOIN table2 AS t2_2 ON t1.whatever = t2_2.whatever
...

Upvotes: 0

Barmar
Barmar

Reputation: 780798

Join with Table 2 twice on different columns.

SELECT t1.*, t2.name AS group_name, t3.name AS member_name
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.group = t2.id
JOIN Table2 AS t3 ON t1.members = t3.id

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

SELECT
  Table_1.*,
  g.Name,
  m.Name
FROM
  Table_1
  INNER JOIN Table_2 AS g ON Table_1.Group=g.ID
  INNER JOIN Table_2 AS m ON Table_1.Group=m.ID
WHERE
  Table_1.Group=2
  AND Table_1.Member=7

Upvotes: 1

Related Questions