SQL query for key value table with 1:n relation

I have a table in which I want to store images. Each image has arbitrary properties that I want to store in a key-value table.

The table structure looks like this

id	fk_picture_id	key	value
1	1	camera	iphone
2	1	year	2001
3	1	country	Germany
4	2	camera	iphone
5	2	year	2020
6	2	country	United States

Now I want a query to find all pictures made by an iphone I could to something like this

select
    fk_picture_id
from
    my_table
where
    key = 'camera'
    and
    value = 'iphone';

This works without any problems. But as soon as I want to add another key to my query I am get stucked. Lets say, I want all pictures made by an iPhone in the year 2020, I can not do something like

select
    distinct(fk_picture_id)
from
    my_table
where
    (
        key = 'camera'
        and
        value = 'iphone'
    )
    or
    (
        key = 'year'
        and
        value = '2020'
    )

…because this selects the id 1, 4 and 5.

At the end I might have 20 – 30 different criteria to look for, so I don’t think some sub-selects would work at the end.

I’m still in the design phase, which means I can still adjust the data model as well. But I can’t think of any way to do this in a reasonable way – except to include the individual properties as columns in my main table.

Answer

The solution I found – thanks to this article How to query data based on multiple ‘tags’ in SQL?

is that I made some changes to the database model

picture

id	name
1	Picture 1
2	Picture 2

And then I created a table for the tags

tag

id	tag
100	Germany
101	IPhone
102	United States

And the cross table

picture_tag

fk_picture_id	fk_tag_id
1	100
1	101
2	101
2	102

For a better understanding of the datasets

Picture	Tagname
Picture 1	Germany & Iphone
Picture 2	United States & IPhone

Now I can use the following statement

SELECT *
  FROM picture
  INNER JOIN (
   SELECT fk_picture_id
   FROM picture_tag
   WHERE fk_tag_id IN (100, 101)
   GROUP BY fk_picture_id
   HAVING COUNT(fk_tag_id) = 2
  ) AS picture_tag
 ON picture.id = picture_tag.fk_picture_id;

The only thing I need to do before the query is to collect the IDs of the tags I want to search for and put the number of tags in the having count statement.

If someone needs the example data, here are the sql statements for the tables and data

 create table picture (
   id integer,
   name char(100)
 );

 create table tag (
  id integer,
  tag char(100)
 );

 create table picture_tag (
  fk_picture_id integer,
  fk_tag_id integer
 );

 insert into picture values (1, 'Picture 1');
 insert into picture values (2, 'Picture 2');

 insert into tag values (100, 'Germay');
 insert into tag values (101, 'iphone');
 insert into tag values (102, 'United States');

 insert into picture_tag values (1, 100);
 insert into picture_tag values (1, 101);
 insert into picture_tag values (2, 101);
 insert into picture_tag values (2, 102);