skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search