Select with joins in a unique table system

I am trying to build an SQL query on an omekaS database to extract books with a specific title and date.

In this system, the table holding the values is called value, and the relevant attributes are as so :

value
-id (pk)
-resource_id
-type (int)
-value

The resource_Id is the identifier through the db Value is one field; like the book “Moby Dick” written in 1822 (random date) would look like this in db. Type tells us what is it (arbitrarily, 1 is titles and 2 is publishing year)

Id	resource_id	type	value
1	1	1	Moby dick
2	1	2	1822

I need to take all books with a certain title, published in a set of years

Basically

Select all the columns from value
Where value is a title and of type title
And value is in a set of years
And with all these having the same resource_id

I get lost, and I have no idea how to do this.

What would be the best way?

Thanks

Answer

If you need to check more types, you can check with AND EXISTS (SELECT 1 FROM ...WHERE type = 3 AND ...) if the conditions are met

I renemd the table name, it is confusing to use cloumn names and table names with identical names.

CREATE TABLE books (
  `Id` INTEGER,
  `resource_id` INTEGER,
  `type` INTEGER,
  `value` VARCHAR(20)
);

INSERT INTO books
  (`Id`, `resource_id`, `type`, `value`)
VALUES
  ('1', '1', '1', 'Moby Dick'),
  ('2', '1', '2', '1822');

SELECT `resource_id`, `value` FROM books 
WHERE `type` = 1 AND `resource_id` IN (SELECT `resource_id` FROM books WHERE `type`= 2 AND `value` IN ('1822','1984'))

resource_id | value    
----------: | :--------
          1 | Moby Dick

db<>fiddle here

And if you want to have them in a row

SELECT b1.`resource_id`, b1.`value` , b2.`value`
FROM books b1 INNER JOIN books b2 ON b1.`resource_id` = b2.`resource_id` and b2.`type`= 2 
WHERE b1. `type` = 1 ANd b1.`resource_id` IN (SELECT `resource_id` FROM books WHERE `type`= 2 AND `value` IN ('1822','1984'))

resource_id | value     | value
----------: | :-------- | :----
          1 | Moby Dick | 1822 

db<>fiddle here