I have the following tables:
CREATE TABLE books
(
codBook INTEGER PRIMARY KEY,
title CHAR(20) NOT NULL
);
INSERT INTO books
VALUES (1, 'Book 1'), (2, 'Book 2'), (3, 'Book 3');
CREATE TABLE people
(
name CHAR(10) PRIMARY KEY,
address VARCHAR(50),
CP NUMERIC(5)
);
INSERT INTO people
VALUES ('Carl', 'C/X nº 1', '12345'), ('Louis', 'C/X nº 2', '12345'),
('Joseph', 'C/Y nº 3', '12346'), ('Anna', 'C/Z nº 4', '12347');
CREATE TABLE lends
(
codBook INTEGER REFERENCES books,
member CHAR(10) REFERENCES people,
date DATE,
PRIMARY KEY (codBook, member, date)
);
INSERT INTO lends
VALUES (1, 'Joseph', CURRENT_DATE - 10),
(1, 'Carl', CURRENT_DATE - 9),
(1, 'Louis', CURRENT_DATE - 8),
(2, 'Joseph', CURRENT_DATE - 10);
I am trying to get all the rows with the title, address and CP where they were borrowed only if they were borrowed in CP=12345 and the rows that are not from CP 12345 to appear but without the address and the CP. As book 1 has CP 12345 and 12346, I only want it to appear with CP 12345.
My expected solution is:
"Book 1";"C/X nº 1";12345
"Book 1";"C/X nº 2";12345
"Book 2";null;null
"Book 3";null;null
I tried joining all the tables using 2 left joins:
SELECT title, address, CP
FROM books
LEFT JOIN lends USING (codBook)
LEFT JOIN people ON (name = member)
WHERE CP = 12345;
But I only get the rows with CP=12345 and if I remove WHERE CP=12345
I obtain all the rows, even the book 1 with CP 12346. I am looking for a way to solve this.
2
Answers
I hope this query will solve your problem:
If you join LENDS and PEOPLE first as
INNER JOIN
and add the CP number to the ON clause you get your resultfiddle