postgresql select fields from 2 tables where data in 2 tables

Question

I'm a total postgresql noob...

I have 3 tables : Documents, Keywords and the join table Documents_Keywords

update

I want to select the id and description from Documents and the keywords from keywords where the description or the keywords are like "certain" and "words" and are not "certain" and "other" and "words".

end of update

The first try I gave to this was :

SELECT actes.id
FROM actes JOIN "actes_motclefs"
    ON "motclefs"."id" = "actes_motclefs"."motclef_id"
WHERE ("motclefs"."motcle" LIKE "%éch%");

Do you really have a many-to-many relationship between documents and keywords while a row in keywords still can contain multiple words? Or you just want to search for word parts too? — pozs
– pozs, Commented Jan 25, 2017 at 13:54
Yes I have a many-to-many relationship. A row in keywords can't contain multiple words. I've updated the question. I now do this : SELECT DISTINCT "documents"."id","documents"."document_date" FROM "documents" INNER JOIN "documents_keywords" ON "documents_keywords"."document_id" = "document"."id" INNER JOIN "keywords" ON "keywords"."id" = "documents_keywords"."keyword_id" WHERE (document_date BETWEEN '900' AND '1400') AND (keyword = 'prébende'); I tried to make that last bit AND (keyword = 'prébende') AND (keyword = 'pagus') but that's not the way to do this. — thiebo
– thiebo, Commented Jan 25, 2017 at 14:15
@thiebo HAVING COUNT(keywords.id) = 2 as I wrote. It will ensure, that only those documents will be returned, which have 2 matching keywords, each of which is either stone OR brick (or in other words: both of them). -- OFC, it will only work, when keyword is unique in keywords and the documents_keywords junction table only contains unique relations. -- you could use HAVING COUNT(DISTINCT keyword) = 2 otherwise. — pozs
– pozs, Commented Jan 25, 2017 at 15:29

Jessica Rucinski · Accepted Answer · 2017-01-25 13:04:12Z

2

SELECT d.id, d.description, k.keywords
FROM documents d
JOIN documents_keywords dk
    ON d.id = dk.document_id
JOIN keywords k
    ON dk.keyword_id = k.id
WHERE (d.description LIKE '%certain%words%'
       OR k.keywords LIKE '%certain%words%') 
AND d.description NOT LIKE '%certain%other%words%'
AND k.keywords NOT LIKE '%certain%other%words%'

answered Jan 25, 2017 at 13:04

Jessica Rucinski

212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thiebo Over a year ago

Thank you! I wasn't clear. Each keyword is a separate entry into the keywords table. So keywords can't be '%certain%other%words%'. I tried WHERE ("k"."keywords" = 'certain' AND "k"."keywords" = 'other' AND "k"."keywords" = 'words') That clearly isn't working.

Collectives™ on Stack Overflow

postgresql select fields from 2 tables where data in 2 tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related