Sql Subquery Question
I have a开发者_JAVA百科 table of data that contains 3 columns that refer to the id's of another table.
table1:
id
, person1_id
, person2_id
, person3_id
people:
id
, fname
, lname
what is the most efficient way of returning fname
and instead of person1_id
in my query?
SELECT
a.id,
x.fname,
y.fname,
z.fname
FROM
table1 a
INNER JOIN people x ON x.id = a.person1_id
INNER JOIN people y ON y.id = a.person2_id
INNER JOIN people z ON z.id = a.person3_id
Not sure if I understood the question well, but:
SELECT table2.data1 FROM table1 INNER JOIN table2 WHERE table1.o1=table2.id
This is a guess about how you want the data formatted, but it sounds like for every record in table1, you want to replace the ID with the first name from the people table.
Try this:
SELECT
id,
(SELECT fname FROM people WHERE id = person1_id) as Person1_fname,
(SELECT fname FROM people WHERE id = person2_id) as Person2_fname,
(SELECT fname FROM people WHERE id = person3_id) as Person3_fname
FROM
table1
This will give you all of the records in table1 and 'replace' the id's with the first names, like so:
1, George, Chris, Judy
2, Sam, Jennifer, Sara
With a table1 of
1, 1, 2, 3
2, 4, 5, 6
and a people table of
1, George, Carlin
2, Chris, Rock
3, Judy, Smith
4, Sam, Elliot
5, Jennifer, Garner
6, Sara, Johnson
I'm assuming there is a unique constraint on people.id as well. If not, you'll want to put a TOP 1 before fname in the 3 field queries.
Did you mean something like this:
SELECT table1.id, table2.data1 FROM table1 LEFT JOIN table2 ON table1.o1 = table2.id;
?
i think this may help you. i taken table1 as sample
SELECT sample.*,person.fname,p.fname FROM sample left join person on sample.p1=person.id, sample s left join person p on s.p2=p.id group by sample.id
精彩评论