Re: Convert 2 foreign key values within the same SELECT

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(at)gmail(dot)com> 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(at)postgresql(dot)org)
> 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.