Hi Frank,
Frank Rust wrote:
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?
It is definitely possible to write these as single queries and there are
many ways to do it. For most queries involving an AND relationship
between two rows, you will end up using an INNER JOIN. For those where
you want "value exists here but not there" queries then you will be
using one of the OUTER JOIN operators.
Here is one way to rewrite your first problem (find articles with both
'peter' and 'mary' as authors):
SELECT DISTINCT a1.article_id
FROM author a1
INNER JOIN author a2
ON a1.id != a2.id
AND a1.article_id = a2.article_id
WHERE a1.name in ('Peter','Mary');
And one way to encode your second problem (articles with 'peter' that
did not also include 'tom')
SELECT DISTINCT a1.article_id
FROM author a1
LEFT JOIN author a2
ON a2.name = 'Tom'
AND a1.article_id = a2.article_id
WHERE a1.name = 'Peter'
AND a2.id is NULL;
I hope this gives you some ideas of the patterns you can use to solve
these types of problems.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
