SQL Query and Many-to-Many relationships

[slick8086]

MySQL 3.23
PHP 4

I have a database with tables article ,cat , artcatRES.

articles can be in more than one catagory and catagories have more than one article.

how can I select article that are in multiple catagories? For instance... catagories are:

news, technology, games, movies

and I want to find articles that are in at least both games and movies. (it is ok if they are in others as well... for now)

My first atempt was:
SELECT article.name, cat.name
FROM article, cat, artcatRES
WHERE
artcatRES.artID = article.artID
AND
artcatRES.catID = cat.catID
AND
cat.name = "games"
AND
cat.name = "movies"

but then I realized that no one record is going to have both catagories. I know I've done this before, but I can't remember how to do it. I can't use sub-selects. Using OR between the catagories doesn't return the desired results. What am I missing?

[dopey]

think ur gonna have to join the category table twice to find if it's in 2 categories

SELECT 
    article.name, cat.name 
FROM 
    article, cat1, cat2, artcatRES 
WHERE 
    artcatRES.artID = article.artID 
    AND
    artcatRES.catID = cat1.catID 
    AND
    artcatRES.catID = cat2.catID 
    AND 
    cat1.name = "games" 
    AND 
    cat2.name = "movies";

[devinemke]

SELECT article.name, cat.name 
FROM article, cat, artcatRES 
WHERE 
artcatRES.artID = article.artID 
AND 
artcatRES.catID = cat.catID 
AND cat.name IN("games", "movies")

[dopey]

i think slick8086 is after code where the record exists in both categories - devinemke, i think ur code will include resuts that exist in games or movies, not exclusively both. Please correct me if i'm wrong...

[slick8086]

Thanks for the help guys, but unfortunatly neither method worked... I tried adjusting it around, putting parenthesis around different groups of statements, but nothing worked. I think I'm going to try selecting all the records with either tag and then use php to test if both exist...
And you are right dopey I want stories that have both catagories.

The only way I can figure that it will work is to select the articles that have one catagory, and then select from that list all the articles that have the second catagory. I can't figure how that can be done with one select. hhhmmmm.

SELECT 
    article.name, cat.name 
FROM 
    article, cat1, cat2, artcatRES 
WHERE 
    artcatRES.artID = article.artID 
    AND
    artcatRES.catID = cat1.catID #<---- 
    AND                          #     |-these can't both match
    artcatRES.catID = cat2.catID #<---- 
    AND 
    cat1.name = "games" 
    AND 
    cat2.name = "movies";

[dopey]

d'oh! of course they won't.

Assuming artcatRES is a lookup table, linking articles to a category, the following should do it...

SELECT  

    article.name, cat.name  

FROM  

    article, artcatRES as artcatRES1, artcatRES as artcatRES2, cat as cat1, cat as cat2 
WHERE  

    artcatRES1.artID = article.artID
    AND
    artcatRES2.artID = article.artID 
    AND
    artcatRES1.catID = cat1.catID 
    AND               

    artcatRES2.catID = cat2.catID 
    AND  

    cat1.name = "games"  

    AND  

    cat2.name = "movies";

[slick8086]

Originally posted by dopey
d'oh! of course they won't.

Assuming artcatRES is a lookup table, linking articles to a category, the following should do it...

I had already tried that, but thanks... The dilemma is that the search requires matching a set of articles to a set of catagories (basically the definition of a many to many relationship). Unfortunately your method returns either or results as well. I'm going to keep banging away at it.

[dopey]

I think I must be mis-understanding how your tables are constructed - I have constructed a similar setup here and that query only pulls out articles in both categories...

CREATE TABLE `articles` ( `artID` tinyint(3) unsigned NOT NULL auto_increment, `content` varchar(255) default '0', PRIMARY KEY  (`artID`)) TYPE=MyISAM;

CREATE TABLE `cats` ( `catID` tinyint(3) unsigned NOT NULL auto_increment, `name` char(255) default '0', PRIMARY KEY  (`catID`)) TYPE=MyISAM;

CREATE TABLE `articleRES` ( `linkID` tinyint(3) unsigned NOT NULL auto_increment, `artID` tinyint(3) unsigned default '0', `catID` tinyint(3) unsigned default '0', PRIMARY KEY  (`linkID`)) TYPE=MyISAM;

INSERT INTO `articles` VALUES("1", "article1 - only games");
INSERT INTO `articles` VALUES("2", "article2 - only movies");
INSERT INTO `articles` VALUES("3", "article3 - both games and movies");

INSERT INTO `cats` VALUES("1", "games");
INSERT INTO `cats` VALUES("2", "movies");

INSERT INTO `articleRES` VALUES("1", "1", "1");
INSERT INTO `articleRES` VALUES("2", "2", "2");
INSERT INTO `articleRES` VALUES("3", "3", "1");
INSERT INTO `articleRES` VALUES("4", "3", "2");

SELECT   

    articles.content, cat1.name, cat2.name   

FROM   

    articles, articleRES as artcatRES1, articleRES as artcatRES2, cats as cat1, cats as cat2  

WHERE   

    artcatRES1.artID = articles.artID 
    AND 
    artcatRES2.artID = articles.artID  

    AND 
    artcatRES1.catID = cat1.catID  

    AND                

    artcatRES2.catID = cat2.catID  

    AND   

    cat1.name = "games"   

    AND   

    cat2.name = "movies"
    AND
    cat1.catID != cat2.catID;

ah - could have been the missing cat1.catID != cat2.catID that might have caused dups.

all this is assuming u know how many categories the article is in to begin with - that is how many times u'll have to link the articleRes/cat tables.