CODEWITHSUNDEEP
sqloracle-database
bgoosman
bgoosman

Reputation: 727

What's the right way to write this query in SQL?

I have two tables:

Names
- id (int)
- name (string)

and

Relationships
- id1 (int)
- id2 (int)

I want to query a list of all relationships that have certain id1, and I want to include the names from Names in the resulting query.

So, the result would have four columns

Is this possible? I know I can do an inner join to include one of the names, but I am not sure how to include both names.

For one name I would do something like:

select Relationships.id1, Relationships.id2, Names.name from Relationships
inner join Names
on Names.id1 = Relationships.id1

Upvotes: 2

Views: 74

Answers (2)

Sean McSomething
Sean McSomething

Reputation: 6507

You want to do a second join to Names. You can use table aliases to specify which join is which.

select r.id1, r.id2, n1.name, n2.name
from Relationships r
inner join Names n1
    on n1.id = r.id1
inner join Names n2
    on n2.id = r.id2

Upvotes: 1

Matt Dodge
Matt Dodge

Reputation: 11142

You can join a table twice.

SELECT
   r.id1,
   r.id2,
   n1.name,
   n2.name
FROM Relationships r
INNER JOIN Names n1 ON r.id1 = n1.id
INNER JOIN Names n2 ON r.id2 = n2.id

Upvotes: 4

Related Questions