Search code examples
mysqlsqlomeka

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

Solution

  • 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