How can I get certain columns from a table only if they meet a condition in SQL?-CodePudding

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.

CodePudding user response：

If you join LENDS and PEOPLE first as INNER JOIN and add the CP number to the ON clause you get your result

SELECT title , address, CP 
FROM books 
LEFT JOIN (lends 
INNER JOIN people ON (name = member AND CP = 12345))  USING (codBook)

title	address	cp
Book 1	C/X nº 2	12345
Book 1	C/X nº 1	12345
Book 2	null	null
Book 3	null	null

SELECT 4

fiddle

CodePudding user response：

I hope this query will solve your problem:

select books.title, sub.address, sub.CP
from books 
left join (
    SELECT address, CP, codbook
    FROM books 
    LEFT JOIN lends USING (codBook) 
    JOIN people ON (name = member and CP = 12345) 
) as sub on books.codbook = sub.codbook