CODEWITHSUNDEEP
sqljoin
user7653331
user7653331

Reputation:

SQL Join on multiple columns of the same table

I have two tables for Administrative Divisions as below. I'm trying to get the Municipality Name for each Parish.

municipalities

id       | district_id | name     |
---------|-------------|----------|
01       | 01          | Abc      |
01       | 02          | Cba      |
01       | 03          | Random   |
...

parishes

id  | municipality_id | district_id | name |
----|-----------------|-------------|------|
03  | 01              | 01          | Abc  |
09  | 01              | 02          | Zxe  |
12  | 01              | 01          | Tfg  |
19  | 01              | 03          | Qwe  |

Query that I tried with no success:

SELECT municipalities.Name FROM municipalities
JOIN parishes AS P 
  ON municipalities.id = P.municipality_id
  AND municipalities.district_id = P.district_id;

The names don't match. How can I change the query to have a Municipality name for each Parish? Ideally the result would be something like this:

id | municipality_name | parish_name
03 | Abc               | Abc
09 | Cba               | Zxe
...

Note: The id column for municipalities doesn't have a Primary Key Constraint.

Upvotes: 1

Views: 4942

Answers (3)

kc2018
kc2018

Reputation: 1460

Just add the data field from table parishes

SELECT p.id, municipalities.Name, p.name
FROM municipalities
JOIN parishes AS P 
  ON municipalities.id = P.municipality_id
  AND municipalities.district_id = P.district_id;

Upvotes: 0

wookiekim
wookiekim

Reputation: 1176

Try

SELECT p.id, m.name AS municipality_name, p.name AS parish_name
FROM municipalities m 
  JOIN parishes p ON m.id = p.municipality_id AND m.district_id = p.district_id

Upvotes: 0

Dan Getz
Dan Getz

Reputation: 9136

Just add the other columns you want after SELECT, making a comma-separated list of which columns you want, in the order you want them to be returned:

SELECT P.id, municipalities.name, P.name
  FROM municipalities
  JOIN parishes as P
  ...

Upvotes: 0

Related Questions