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