List:General Discussion« Previous MessageNext Message »
From:Frank Rust Date:November 12 2008 3:59pm
Subject:Single query possible?
View as plain text  
Hi there, I'm just trying to solve a little problem:
There are two tables (simplified):
CREATE TABLE article (ID int, Title varchar, ...other_data );
CREATE TABLE author (ID int,name varchar,article_ID int,...other_data );

Now I have a lot of articles. They have one or more autors, e.g.
INSERT INTO  article (ID,Title)
        VALUES (123,'test article'),
               (124,'another article'),
               (125,'third article');

INSERT INTO author (ID,name,article_ID)
        VALUES (1,'peter', 123),(2,'paul', 123),(3,'mary',123);
INSERT INTO author (ID,name,article_ID)
        VALUES (4,'peter', 124),(5,'paul',124);
INSERT INTO author (ID,name,article_ID)
        VALUES (6,'peter', 125),(7,'mary',125),(8,'tom',125);

I try to build a query to do the following: find all articles where  
'peter' and 'mary' are among the authors;
or find all articles where 'peter' is an author but 'tom' not;
etc.

If there would be no possibility to do that, how could I redesign my tables?

Best regards,
Frank

Thread
Single query possible?Frank Rust12 Nov
  • Re: Single query possible?Shawn Green22 Nov