Question 249783 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Replace LEFT JOIN with WHERE statement

I have DB with 4 tables. I have to use 3 of them.

Is it possible to change LEFT JOIN statements with only WHERE statement?

Database scheme
DB scheme

My code block with LEFT JOIN statements:

FROM        reader
LEFT JOIN   copy
ON          reader.nr = copy.reader
LEFT JOIN   book
ON          copy.ISBN = book.ISBN

Output on this query is the Personal code | Name | Surname | Taken books count | Taken Pages sum | Taken books cost

Total: 8 rows (data is correct)

I have tried to replace it on my own. I am out of ideas now. I was thinking, that it should work like this:

WHERE       copy.ISBN = book.ISBN AND 
            reader.nr = copy.reader AND
            CURRENT_DATE BETWEEN copy.taken AND copy.return

Output on this query should be the same. And it is the same, only the data is not correct. There are only 4 rows and there are people that do not have any books taken right now, but they are not displaying in the output.

It do not work like that. Maybe someone who knows better than me could help with this.

Tags:

3

Answers


  1. 0

    It does work like that but you use an additional:

    CURRENT_DATE BETWEEN copy.taken AND copy.return

    in the second query. That will change the output. Only books that are returned will be in the result list imo

    Login or Signup to reply.
  2. 0

    LEFT JOIN joins the other tables you are using (kinda merges them on in other, using the values from the FROM clause table, in this case reader). If you omit this, and if you only SELECT * FROM reader WHERE ... you will only have the data of the table reader and not the other tables (copy and book) so you wont be able to use copy.reader.

    So no, this cant be done only with WHERE clauses.

    Login or Signup to reply.
  3. 0

    the WHERE removes all rows that fit the bill, that includes all that haven’t a book at the current date, so move the date comparison to the ON clause

    FROM        reader
LEFT JOIN   copy
ON          reader.nr = copy.reader AND CURRENT_DATE BETWEEN copy.taken AND copy.return
LEFT JOIN   book
ON          copy.ISBN = book.ISBN
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search