CODEWITHSUNDEEP
sqlpostgresqljoin
Eric
Eric

Reputation: 1291

Cant figure out whats wrong with my query

I want to find similar books based on purchased books based on book meta keywords but the query below returns books which user already have purchased too. I need to bring books which arent purchased but are similar based on his purchases. Hope thats clear.

Below is the link for query and data and results. https://www.db-fiddle.com/f/tovUePp2WVffXLcuaxmJ8K/3

Query shouldnt return data which returned by this below query. But its returning some of below data too.

SELECT c.book FROM customers_books c WHERE c.customer = 1

Upvotes: 0

Views: 46

Answers (2)

Jayesh Goyani
Jayesh Goyani

Reputation: 11154

Could you please try with below query? I have added left join into the existing query to remove already purchased books.

SELECT bmk2.book
FROM book_meta_keywords bmk2
INNER JOIN book_meta_keywords bmk1 
                ON  bmk2.meta_keyword = bmk1.meta_keyword
INNER JOIN customers_books cb 
                ON bmk1.book = cb.book
INNER JOIN books b ON b.id = bmk2.book
LEFT JOIN customers_books cbp ON cbp.book = b.id 
                    and cbp.customer = 1
WHERE cb.customer = 1 AND b.status = 'PUBLISHED'
and cbp.book IS NULL
GROUP BY bmk2.book
ORDER BY MAX(b.modified_date) DESC ;

Upvotes: 2

g_bor
g_bor

Reputation: 1092

If the two queries are in the same form, you can simply EXCEPT them.

See this for more info: https://www.techonthenet.com/postgresql/except.php

Upvotes: -1

Related Questions