CODEWITHSUNDEEP
sqlpostgresqljpajpql
zemirco
zemirco

Reputation: 16395

PostgreSQL: What's the best way to find rows by two column values?

Let's say I've got software packages and those packages have versions. Each version has its own license.

create table my_package (
    id serial primary key,
    name text
);

insert into my_package (name) values
    ('a'),
    ('b'),
    ('c'),
    ('d');

create table my_version (
    my_package_id int references my_package,
    version text,
    license text
);

insert into my_version (my_package_id, version, license) values
    (1, '1.0.0', 'mit'),
    (1, '2.0.0', 'isc'),
    (1, '3.0.0', 'bsd'),
    (2, '1.0.0', 'foo'),
    (2, '2.0.0', 'bar'),
    (2, '3.0.0', 'baz'),
    (3, '1.0.0', 'aaa'),
    (3, '2.0.0', 'bbb'),
    (3, '3.0.0', 'ccc'),
    (4, '1.0.0', 'ggg'),
    (4, '2.0.0', 'qqq');

My problem is finding the licenses for packages where I have the versions and the names, e.g. a: 2.0.0 and b: 1.0.0.

Here is what I'm currently doing.

select
    my_package.name,
    my_version.version,
    my_version.license
from
    unnest(ARRAY['a', 'b'], array['2.0.0', '1.0.0']) as t(name, version)
join my_package on t.name = my_package.name
join my_version on my_package.id = my_version.my_package_id and t.version = my_version.version;

That returns the result I'm expecting.

|name|version|license|
|----|-------|-------|
|a   |2.0.0  |isc    |
|b   |1.0.0  |foo    |

I'm wondering if there is a simpler way without using unnest and ideally subqueries in the from clause. My goal would be to find a query that works using JPA.

Any ideas? Thank you very much!

Upvotes: 0

Views: 124

Answers (2)

JGH
JGH

Reputation: 17876

You can query a set of column together by wrapping them in parentheses:

select
    my_package.name,
    my_version.version,
    my_version.license
from my_package p
 join my_version v on p.id = v.my_package_id 
WHERE
 (p.name,v.version) in (('a','2.0.0'),('b','1.0.0'));

Upvotes: 2

melcher
melcher

Reputation: 1601

My problem is finding the licenses for packages where I have the versions and the names, e.g. a: 2.0.0 and b: 1.0.0.

I was wondering why you wouldn't just query those values in the where clause and wrote this reply... and just noticed the reference to JPA, which is a package I'm not familiar with. In that case, maybe this isn't helpful but figure i'd post it anyway.

select
    my_package.name,
    my_version.version,
    my_version.license
FROM my_package
INNNER JOIN my_version on my_package.id = my_version.my_package_id

WHERE my_package.name = 'a' AND my_version.version = '2.0.0' 
  OR my_package.name = 'b' AND my_version.version = '1.0.0'

Upvotes: 1

Related Questions