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

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!

CodePudding user response：

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'));

CodePudding user response：

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'