What is the syntax for a where clause for many columns from different tables?

Question

I'm creating database for a place which have meeting rooms for reserve & sell books. The schema is genericized to allow reuse of tables for multiple data sets.

The diagram is slightly incorrect; there are two selector tables which actually have distinct names. The selector table in the top right is actually called option_selector and the bottom left is values_selector. I fill the resource_type table with 1, room; 2, book then resource_option with 1, Name; 2, size; 3,Author; 4,Publish Year and fill the option_selector table with the rows.

• 1, 1, 1 for mapping room to name
• 2, 1, 2 for mapping room to size
• 3, 2, 1 for mapping book to name
• 4, 2, 3 for mapping book to author
• 5, 2, 4 for mapping book to Publish Year

I also fill the option_selector and option_value tables with some rows. Then I run this query

SELECT r.id as 'book ID', v.value as 'Name'
FROM resources r
  JOIN value_selector s ON r.id = s.resource_id
JOIN resourse_option_value v ON s.value_id = v.id
WHERE v.option_selector_id = 3
ORDER BY r.id

The result

book ID    Name
5          Journey to the center of the Earth
6          Oliver Travels
7          C How To Program
8          Java How To Program

And that's the right output.

The problem is I want add more data columns to be selectable with the book records, for example 'Author' & 'Publish Year' to make this output table look like

book ID    Name    Author     Publish Year

I tried this

SELECT r.id as 'book ID', v.value as 'Name', v2.value as 'Author', v3.value as 'Year'
FROM resourses r
  JOIN value_selector s ON r.id = s.resourse_id
  JOIN resourse_option_value v ON s.value_id = v.id
  JOIN resourse_option_value v2 ON s.value_id = v2.id
  JOIN resourse_option_value v3 ON s.value_id = v3.id
WHERE
  v.option_selector_id = 3 AND
  v2.option_selector_id = 4 AND
  v3.option_selector_id = 5
ORDER BY r.id

This gives an empty result

Database sql : http://sqlfiddle.com/#!9/302671/3

Answer 1

Your database structure is still not clear to me.

But just to give you an idea where to start you can try this one:

SELECT 
  r.id as 'book ID', 
  MAX(IF(v.option_selector_id = 3,v.value, null)) as 'Name',
  MAX(IF(v.option_selector_id = 4,v.value, null)) as 'Author',
  MAX(IF(v.option_selector_id = 5,v.value, null)) as 'Publish year'
FROM resourses r
JOIN value_selector s
ON r.id = s.resourse_id
JOIN resourse_option_value v
ON s.value_id = v.id
GROUP BY r.id
ORDER BY r.id

I feel that this query will not bring you expected result since it is value depended query and I have no idea what are correct values are. So if you provide more samples of your data we can figure out what is correct query in your case. Please provide samples for all tables and/or sqlfiddle would be perfect.

EDIT 1 So if you need only books, just add WHERE r.type_id = 2clause:

http://sqlfiddle.com/#!9/302671/12

SELECT 
  r.id as 'book ID', 
  MAX(IF(v.option_selector_id = 3,v.value, null)) as 'Name',
  MAX(IF(v.option_selector_id = 4,v.value, null)) as 'Author',
  MAX(IF(v.option_selector_id = 5,v.value, null)) as 'Publish year'
FROM resourses r
JOIN value_selector s
ON r.id = s.resourse_id
JOIN resourse_option_value v
ON s.value_id = v.id
WHERE r.type_id = 2
GROUP BY r.id
ORDER BY r.id

EDIT 2 To get books in specific year you can:

http://sqlfiddle.com/#!9/302671/15

SELECT 
  r.id as 'book ID', 
  MAX(IF(v.option_selector_id = 3,v.value, null)) as 'Name',
  MAX(IF(v.option_selector_id = 4,v.value, null)) as 'Author',
  MAX(IF(v.option_selector_id = 5,v.value, null)) as 'Publish year'
FROM resourses r
JOIN value_selector s
ON r.id = s.resourse_id
JOIN resourse_option_value v
ON s.value_id = v.id
WHERE r.type_id = 2
GROUP BY r.id
HAVING `Publish year`=2014
ORDER BY r.id

Answer 2

Well, Alex's answer shows that the schema is workable. It's still pretty crazy, in my opinion. In particular, needing the MAX and HAVING clauses in what should be simple lookup statements are very concerning for the scalability. For comparison, consider the schema with separate tables as proposed by Jay instead.

CREATE TABLE `book` (
  `bookId` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  `author` VARCHAR(45) NOT NULL,
  `publishYear` INT NOT NULL,
  PRIMARY KEY (`bookId`));

INSERT INTO `book` (`title`, `author`, `publishDate`) VALUES
("Journey to the center of the Earth", "Johny", 2013),
("Oliver Travels", "Ahmed Ali", 2015);

CREATE TABLE `room` (
  `roomId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL
  PRIMARY KEY (`roomId`));

INSERT INTO `room` (`name`) VALUES
  ("Library"), ("Study"), ("Hall"), ("Dining Room"), ("Kitchen"), ("Conservatory"), ("Billiard Room"), ("Lounge"), ("Ballroom");

And you're done. No nulls (which aren't a big problem in MySQL anyways, and actually reduce storage requirements on the InnoDB engine), no foreign keys, half the SQL to pull it off. You can easily tell what any data record is by its table name and ID without having to check three different tables for column names, option names and values.

Need the books?

SELECT `bookId`, `title`, `author`, `publishYear` FROM `book`;

From a certain year? (Might want to add an index on the publish year.)

SELECT `bookId`, `title`, `author`, `publishYear` FROM `book`
WHERE `publishYear` = 2013;

Need to allow books to have multiple authors?

CREATE TABLE `author` (
  `authorId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45),
  `bookId` INT NOT NULL,
  PRIMARY KEY(`authorId`));

ALTER TABLE `book` DROP COLUMN `author`;

SELECT `b`.`bookId`, `title`, GROUP_CONCAT(`name`) as authors, `publishYear`
FROM `book` b
LEFT JOIN `author` a ON `a`.`bookId` = `b`.`bookId`
GROUP BY `b`.`bookId`;

Add foreign keys if you're so inclined.