1

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?

1

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
...
  • 1
    "Some databases"... Call me Oracle ;) – ypercubeᵀᴹ Jun 23 '18 at 18:22
  • 1
    @ypercubeᵀᴹ: WebUserLearner is clearly not using Oracle given the invalid use of a string literal for an alias ( AS 'primary') – a_horse_with_no_name Jun 24 '18 at 8:37
  • I had to change relationships.related_person_id AS relatedto to r.related_person_id AS relatedto but otherwise it worked! Thanks. – WebUserLearner Jun 26 '18 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.