5

I've got 2 SQL tables: NAMES and RELATIONSHIPS Currently NAMES just has 2 columns: name and name_id. The other is a list of relationships between people in the NAMES table. It has 3 columns: primaryperson_id, relatedperson_id, and relationship_id. The primaryperson_id and related_person_id are name_ids from the NAMES table. Each person in NAMES can have multiple entries in either the primary or related columns of RELATIONSHIPS (is that a many-to-many relationship?).

This query works: 

SELECT  people.name AS 'primary', relationships.related_person_id AS relatedto
FROM relationships
JOIN people
ON people.name_id=relationships.primary_person_id 
ORDER BY people.name_id;

But I'd like to show a name (i.e. text) in the relatedto column rather than the id number. How can I do that?

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
WebUserLearner
  • 153
  • 1
  • 3

1 Answers1

4

You can assign a table alias when you join tables, just like you do with the column names. Not only does it save you a lot of typing, but it makes the code a little more readable, and it solves the problem of joining the same table twice.

SELECT p1.name AS "primary",
       relationships.related_person_id AS relatedto,
       p2.name AS related_name
FROM people AS p1
INNER JOIN relationships AS r
    ON p1.name_id=r.primary_person_id 
INNER JOIN people AS p2
    ON p2.name_id=r.related_person_id 
ORDER BY p1.name_id;

In the example above, I've assigned p1 and p2 to the people table and r to the relationships table.

Note: some database platforms don't like the "AS" keyword. You can skip "AS" alltogether if you want, like so:

...
FROM people p1
INNER JOIN relationships r
...
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Daniel Hutmacher
  • 9,113
  • 1
  • 25
  • 52