CODEWITHSUNDEEP
sql
Adi
Adi

Reputation: 3

Select from two different tables

So, I have two tables, Table R and Table N. The data in Table R is from Table N. My problem is I don't know the SELECT query that will display the result such as below, because there are 4 names, and SQL can only choose 1. Is there a query or any other way to get the result?

Table R:
Id1 Id2 Id3 Id4
1   3   5   7
2   4   6   8

Table N:
Id  Name
1   A
2   B
3   C
4   D
5   E
6   F
7   G
8   H

After the SELECT statement, the result should look like this:

Name1   Name2   Name3   Name4
A       C       E       G
B       D       F       H

Anyway, thanks for helping. ^_^

Upvotes: 0

Views: 1261

Answers (3)

Jan Hudec
Jan Hudec

Reputation: 76386

  1. You can rename the table in the query

  2. You can join the same table multiple times provided you rename it, so:

    select N1.Name, N2.Name, N3.Name, N4.Name
from R
join N as N1 on N1.Id = Id1
join N as N2 on N2.Id = Id2
join N as N3 on N3.Id = Id3
join N as N4 on N4.Id = Id4

    (of course that's equivalent to

    select N1.Name, N2.Name, N3.Name, N4.Name
from R, N as N1, N as N2, N as N3, N as N4
where N1.Id = Id1 and
    N2.Id = Id2 and
    N3.Id = Id3 and
    N4.Id = Id4

    (the on clauses are just added to where and comma is join)

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116200

select
  n1.Name as Name1,
  n2.Name as Name2,
  n3.Name as Name3,
  n4.Name as Name4
from
  R
  inner join N n1 on n1.id = R.id1
  inner join N n2 on n2.id = R.id2
  inner join N n3 on n3.id = R.id3
  inner join N n4 on n4.id = R.id4

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52382

SELECT
  N1.Name AS `Name1`,
  N2.Name AS `Name2`,
  N3.Name AS `Name3`,
  N4.Name AS `Name4`
FROM
  R
INNER JOIN
  N N1
ON
  N1.Id = R.Id1
INNER JOIN
  N N2
ON
  N2.Id = R.Id2
INNER JOIN
  N N3
ON
  N3.Id = R.Id3
INNER JOIN
  N N4
ON
  N4.Id = R.Id4

Upvotes: 3

Related Questions