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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.