1
create table sections(
  id serial,
  section_name char(255) not null,
  version_id int not null
);

create table section_versions(
 id int primary key not null,
 version_name char(255) not null  
);

insert into section_versions(id, version_name)
values (1, 'default'), (2, 'version A'), (3, 'version B');

insert into sections(section_name, version_id)
values ('Toys', 1), ('Animals', 1), ('Cars', 1),
       ('Toys', 2), ('Animals', 2), ('Instruments', 2),
       ('Toys', 3);

I need to select sections based on requested section_version.version_name.

If the version_name is "default", then query just need to return all sections which have "default" version.

But if the "version A" was requested, then it should return every section that belongs to "version A", and add a missing sections from "default" version - basing on section_name.

Please see this fiddle: http://sqlfiddle.com/#!15/466e1/1/0

Here's what I came up with:

select * from sections
join section_versions on (section_versions.id = sections.version_id)
where section_versions.version_name = 'default'

and sections.section_name not in (
  select sections.section_name from sections
  join section_versions on (section_versions.id = sections.version_id)
  where section_versions.version_name = 'version A'
)

UNION

select * from sections
join section_versions on (section_versions.id = sections.version_id)
where section_versions.version_name = 'version A'
;

This is probably a naive try, so I am looking for a better solution.

It would be nice to have one query that will handle:

  1. Selecting only "default"
  2. Selecting a specific version
  3. Working when no default version exists (f.i. like Instruments)
Ernest
  • 8,701
  • 5
  • 40
  • 51

1 Answers1

1

If I understood your intention properly following query should help:

select distinct on (sections.section_name) * 
from sections
join section_versions on (section_versions.id = sections.version_id)
where 
  section_versions.version_name in ('default', 'version A')
order by
  sections.section_name,
  case version_name
    when 'default' then 1
    else 0
  end;

For more info see "DISTINCT Clause" paragraph in http://www.postgresql.org/docs/current/static/sql-select.html

Radek Postołowicz
  • 4,506
  • 2
  • 30
  • 47
  • Really nice, thank you. However, even if I knew about distinct, I still have trouble understanding how it works. I guess "the secret" is in the ORDER BY part? – Ernest Apr 20 '15 at 15:29
  • 1
    @Ernest yes ORDER BY is crucial in query above. DISTINCT ON removes duplicated rows using passed column list (in this example column list consist of `sections.section_name` only) to check whether rows are duplicated. For each group of duplicated records only the first one is left out, others are removed from final output. That's why we have to ensure that rows with `version_name` other than default (if present at all) appear before defaults. Ordering by section_name is a postgresql constraint (columns used in DISTINCT ON have to appear in ORDER BY first). Really important is CASE clause. – Radek Postołowicz Apr 21 '15 at 12:53
  • You can see second answer at http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group for more extensive explanation also. – Radek Postołowicz Apr 21 '15 at 15:57
  • Thank you very nuch @Radek, I understand it very well now. And your solution works very well. – Ernest Apr 21 '15 at 21:49