Search code examples
sqlpostgresqlinner-join

ERROR: invalid reference to FROM-clause entry for table "oth"

I have a problem with this query

SELECT DISTINCT(oth.book) FROM book_meta_keywords oth, 
                (SELECT bmk.meta_keyword AS metaKeyword, bmk.book AS book FROM books b
                    INNER JOIN customers_books cvb ON cvb.book = b.id
                    INNER JOIN book_meta_keywords bmk ON bmk.book = b.id
                    WHERE cvb.customer = 1 ) AS allCustomerPurchasedBooksMeta 
            INNER JOIN books b ON b.id = oth.book
            WHERE oth.meta_keyword = allCustomerPurchasedBooksMeta.metaKeyword AND oth.book != allCustomerPurchasedBooksMeta.book AND b.status = 'GOOD'

I am getting below error for this query.

ERROR: invalid reference to FROM-clause entry for table "oth" LINE 6: INNER JOIN books b ON b.id = oth.book ^ HINT: There is an entry for table "oth", but it cannot be referenced from this part of the query. , Time: 0.002000s

But if I run the below query it works

SELECT DISTINCT(oth.book) FROM book_meta_keywords oth, 
                (SELECT bmk.meta_keyword AS metaKeyword, bmk.book AS book FROM books b
                    INNER JOIN customers_books cvb ON cvb.book = b.id
                    INNER JOIN book_meta_keywords bmk ON bmk.book = b.id
                    WHERE cvb.customer = 1 ) AS allCustomerPurchasedBooksMeta 
            WHERE oth.meta_keyword = allCustomerPurchasedBooksMeta.metaKeyword AND oth.book != allCustomerPurchasedBooksMeta.book

Can anyone help me why... query is basically trying to get similar books based on purchased books based on their meta keywords.

thanks.

Solution

  • This is your FROM clause:

    FROM 
    book_meta_keywords oth, 
    (SELECT ... FROM ... WHERE ...) AS allCustomerPurchasedBooksMeta 
    INNER JOIN books b ON b.id = oth.book

    You are mixing explicit and implicit joins (the latter is denoted by the comma). Don't. They have different prescendence rules and the query planner ends up evaluating the the second condiiton before oth was seen.

    As for how to solve this: assuming that the logic is indeed what you want, that's a lateral join:

    FROM 
    book_meta_keywords oth 
    CROSS JOIN LATERAL (SELECT ... FROM ... WHERE ...) AS allCustomerPurchasedBooksMeta 
    INNER JOIN books b ON b.id = oth.book

    I suspect, however, that your query could be further simplified. You might want to ask another question for this, explaning the purpose of the query and providing a minimum reproducible example.