Postgres Pro
Downloads
Home   >   mailing lists

Thread: using a common key value on both sides of a union ?

using a common key value on both sides of a union ?

From
David Gauthier
Date:
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 !

Thanks in Advance 


Re: using a common key value on both sides of a union ?

From
"David G. Johnston"
Date:
On Wednesday, April 1, 2020, David Gauthier <davegauthierpg@gmail.com> 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 !

You are correct this model seems undesirable but given you have it why doesn’t the following work for you?

Select students where class = ‘math’
Union all
Select all_classes where class = ‘math’

The generally better query is:
Select from students ... group by rollup


David J.

Re: using a common key value on both sides of a union ?

From
Laurenz Albe
Date:
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,
arecord 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