From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | David Gauthier <davegauthierpg(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: using a common key value on both sides of a union ? |
Date: | 2020-04-02 08:53:28 |
Message-ID: | 1a41f87c2a6e07ca56f3974067b50f51ac1a2630.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2020-04-01 at 19:39 -0400, David Gauthier wrote:
> psql (9.6.7, server 11.3) on linux
>
> I want to do something like this
> (intentionally bad sql but will illustrate the need)
>
> select s.name,s.grade from students s where s.class='math'
> union
> select 'whole class', class_grade from all_classes where class=s.class
>
> Of course it's that "where class=s.class" that's a foul.
>
> In English, I want a list of each math student and their grade and then append one more record for the entire class, a record that comes from a different table but narrowed down to that one class.
>
> I don't care if union isn't the right approach. Anything that works is welcome !
SELECT s.name, s.grade
FROM students s
WHERE s.class = 'math'
UNION
SELECT 'whole class', a.class_grade
FROM all_classes a
WHERE EXISTS (SELECT 1 FROM students s1
WHERE a.class = s1.class
AND s1.class = 'math');
I deliberately ignored that the condition could be simplified substantially.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2020-04-02 14:48:59 | Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12 |
Previous Message | Kyotaro Horiguchi | 2020-04-02 08:25:40 | EINTR while resizing dsm segment. |