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

On Wednesday, April 1, 2020, David Gauthier <davegauthierpg(at)gmail(dot)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

https://www.postgresql.org/docs/9.6/queries-table-expressions.html#QUERIES-GROUPING-SETS

David J.