Thread: Convert 2 foreign key values within the same SELECT

I have a query which successfully retrieves id values "me" and "you"
when 2 planetary values are supplied:

SELECT l.me_id AS me, l.you_id AS you,
                 a.l AS left, a.aspect, a.r AS right, l.id AS link_id,
c.comment
FROM aspects a, links_aspects la, links l, syn_comments c
WHERE (a.r = 'Venus' AND a.l = 'Venus')
   AND a.type = 'synastry'
   AND la.aspect_id = a.id
   AND la.id = c.links_aspects_id
   AND la.link_id = l.id

  me  | you | left  | aspect | right | link_id |          comment
-----+-----+-------+--------+-------+---------+---------------------------
  365 | 371 | Venus | cnj    | Venus |      49 | Garry/Dom Venus/Venus syn
(1 row)


..... however, l.me_id and l.you_id are foreign keys referencing a users
table from which I would like to retrieve concat(u.first_name, ' ',
u.surname) AS name for l.me_id and l.you_id. It seems simple but I can't
think of a JOIN which will do the trick.

gvim

On 01/04/2016 12:36 PM, gvim wrote:
> I have a query which successfully retrieves id values "me" and "you"
> when 2 planetary values are supplied:
>
> SELECT l.me_id AS me, l.you_id AS you,
>                 a.l AS left, a.aspect, a.r AS right, l.id AS link_id,
> c.comment
> FROM aspects a, links_aspects la, links l, syn_comments c
> WHERE (a.r = 'Venus' AND a.l = 'Venus')
>   AND a.type = 'synastry'
>   AND la.aspect_id = a.id
>   AND la.id = c.links_aspects_id
>   AND la.link_id = l.id
>
>  me  | you | left  | aspect | right | link_id |          comment
> -----+-----+-------+--------+-------+---------+---------------------------
>
>  365 | 371 | Venus | cnj    | Venus |      49 | Garry/Dom Venus/Venus syn
> (1 row)
>
>
> ..... however, l.me_id and l.you_id are foreign keys referencing a
> users table from which I would like to retrieve concat(u.first_name, '
> ', u.surname) AS name for l.me_id and l.you_id. It seems simple but I
> can't think of a JOIN which will do the trick.
>
> gvim
>
>
Join to user table twice, once for you once for me?