How do I get a query result to be put into the result of another query?

Question

I have a table that looks like this:

Persons:

+---------+----------+----------+
| ID      | name     | clientID |
+---------+----------+----------+
|001      | carl     |002       |
|002      | fred     |null      |
+---------+----------+----------+

I want the result to be:

+---------+----------+
| name    | client   |
+---------+----------+
|carl     | fred     |
+---------+----------+

I can get the output of people with clients with that clients ID (with the following) but not sure how to get the clients name to replace it.

select name, client
from schema.persons
where client is not null

thanks in advance.

you'd need a self-join, to boot: `select * from persons left join persons as client on persons.clientID = clients.ID` — Marc B, Sep 15 '15 at 21:48
possible duplicate of [Self join to a table](http://stackoverflow.com/questions/3035105/self-join-to-a-table) — zimdanen, Sep 15 '15 at 21:50

score 4 · Answer 1 · answered Sep 15 '15 at 21:49

4

You can implement a self join

SELECT p.name,
       c.name as client
  FROM schema.persons p
       JOIN schema.persons c
         ON( p.clientID = c.id )

answered Sep 15 '15 at 21:49

Justin Cave

227,342
24
367
384

How do I get a query result to be put into the result of another query?

1 Answers1