Re: Convert 2 foreign key values within the same SELECT

[Melvin Davidson <melvin6925@xxxxxxxxx>]

Does this work?

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,
       concat(m.first_name, ' ', m.surname) AS me_name,
       concat(y.first_name, ' ', y.surname) AS you_name
FROM aspects a,
           links_aspects la,
           links l,
          syn_comments c
  JOIN users m ON l.me = m.id
  JOIN users y on l.you = y.id
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;

On Mon, Jan 4, 2016 at 2:48 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:

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?

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.