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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: using a common key value on both sides of a union ?
Дата
Msg-id CAKFQuwa+5A-GtFW+inV_Z28vg_Wh99-=d71bfFq_kafaL1Psvg@mail.gmail.com
обсуждение исходный текст
Ответ на using a common key value on both sides of a union ?  (David Gauthier <davegauthierpg@gmail.com>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: David Gauthier
Дата:
Сообщение: using a common key value on both sides of a union ?
Следующее
От: David Steele
Дата:
Сообщение: Re: It is possible to force periodically switch xlog?