SQL help

colm_c

ok I'm trying to create a related articles column based on categories that the content author selects while they're entering the content of an article.

So I have the following tables in the db 'articles', 'categories', and 'articles_categories_link'.

Authors can select as many categories as they like using checkboxes, which then populates the 'articles_categories_link' table.

Generating a list of articles that match a single category is relatively easy, however how would you get it to match at least 2 (for better accuracy)?

Any ideas welcome.

seamus

Should just be a simple matter of an OR. Post up your existing query...

Smoggy

if your looking to match 1 of 2 columns its an "or" , as seamus suggests
I you want both to match, its an and
if your looking for an article that has 2 or more categories its a count() >= 2

so you will have your select and the inner joins followed by a where categoryname = 'x' AND categoryname = 'y'

Peace

It helps if i read the OP's post properly....

Smoggy

Peace, it seems to be a MANY TO MANY - Hence the "articles_categories_link"

Just to throw the cat among the pigeons - I think the relationship SHOULD be one to many, as your articles can have many categories BUT the categories WONT have many articles.


Artciles
ArticleID

Category
CategoryID
ArticleID_FK

So you would be looking for :

select x from article ( inner join on category ) where catname = 'x' and catname = 'y'

Zaph0d

Find articles about Cats and Dogs:

SELECT articles.id,articles.name
                FROM articles a 
                        INNER JOIN articles_categories_link a1
                               ON a.articleid=a1.articleid
                        INNER JOIN articles_categories_link a2 
                               ON a.articleid=a2.articleid
                        INNER JOIN categories c1 
                               ON a1.categoryid=c1.categoryid
                        INNER JOIN categories c2
                               ON a2.categoryid=c2.categoryid
WHERE c1.categoryname='Dogs'
AND c2.categoryname='Cats'

Zaph0d

How are the categories picked when searching for articles? Is it by clicking on checkboxes from a front end?
Which database are you using and which version?

Smoggy

I was thinking a little more about this and I think you are looking at :

select x from article ( inner join on category ) where CategoryID= 1 and CategoryID = 2

colm_c

It has to be a MANY to MANY relationship because the categories can have many articles assigned to them and the articles can have many categories related to them.

Here's the table structures that I'm using (albeit simplified)

'articles'
aID
content

'categories'
cID
name

'articles_categories_link'
linkID
articleID
catID

The way the current script works is it uses some php to generate a list of categories (1,2,3,4,5) in this case for the article, then runs the following SQL (this is a very simplified version) to get recent related articles

"SELECT DISTINCT FROM articles INNER JOIN articles_categories_link ON articles.aID = articles_categories_link.articleID WHERE articles_categories_link.catID IN (1,2,3,4,5) LIMIT 0,6";

The current database is MySQL.

Checkboxes are used by the content author to select the categories for each article - there are around 20 categories and 500 articles at the moment - so getting articles very closely related with just a single category is not proving very effectice.

I know I could do a matrix of some kind where I would have to generate every possible combination of 2 I could think of e.g. for the cateories 1,2,3,4,5 the sql might be something like
.. (catID = 1 AND catID =2) OR (catID = 1 AND catID = 3) OR (catID = 1 OR catID =4) etc.

which would be a royal pain the ass to generate.

Zaph0d

Right, so

SELECT articles.aID ,COUNT(*)
FROM articles 
INNER JOIN articles_categories_link 
  ON articles.aID = articles_categories_link.articleID 
WHERE articles_categories_link.catID IN (1,2,3,4,5) 
GROUP BY articles.aID 
HAVING COUNT(*)>1

colm_c

Excellent thanks for that Zaph0d.

Zaph0d a.k.a. sql guru.