Hello,
Perfect :-)
Thank you,
Hobbs.
Quoting Peter Brawley <[EMAIL PROTECTED]>:
Richard
>I would like to display all messages which match both 5 and 7 in terms
>of the parent_id, meaning messages 10 and 13 would be displayed.
SELECT f1.child
FROM foo AS f1
INNER JOIN foo AS f2 USING(child)
WHERE f1.parent=5 AND f2.parent=7;
PB
-----
Hobbs, Richard wrote:
Hello,
I have two tables - one containing messages, and another containing links
between messages and other messages in a tree structure (much like a
threaded
mailing list archiving thing). A single message can have multiple "parents"
though, meaning the links table can have several entries for a
single message.
For example:
----------------
child parent
10 5
10 7
11 5
12 7
13 5
13 7
----------------
I would like to display all messages which match both 5 and 7 in
terms of the
parent_id, meaning messages 10 and 13 would be displayed.
I have used the following query:
------------------------------------------------------------
SELECT DISTINCT message.username,message.content
FROM message,links WHERE links.child_id = message.id AND (
links.parent_id = 5 OR links.parent_id = 7
);
------------------------------------------------------------
NOTE: Without the word DISTINCT, if this query finds a message that
matches both
5 AND 7, it will display the message twice. I have obviously used
DISTINCT as an
easy way to get around this problem.
However, this query displays the message if it matches 5 OR 7. I
only want it to
be displayed if it matches 5 AND 7.
However, if i change the word "OR" to "AND", it displays no message at all!
I presume this is because it finds two instances of each message, neither of
which match both 5 and 7 (i.e. the first instance matches 5, but not
7, and the
second instance matches 7, but not 5).
Does anyone know how i can get around this problem?
Thanks in advance to anyone who can help! :-)
Richard.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Richard Hobbs
[EMAIL PROTECTED]
Visit my web sites: http://mysites.mongeese.co.uk
Would you like jokes in your email? http://jokes.fishsponge.co.uk
Would you like to discuss unix/linux? http://ufq.unixforum.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
