List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 18 2005 10:27pm
Subject:Re: Query matches twice, but not simultaneously...
View as plain text  
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

Thread
Query matches twice, but not simultaneously...Richard Hobbs18 Sep
  • Re: Query matches twice, but not simultaneously...Peter Brawley19 Sep
    • Re: Query matches twice, but not simultaneously...Richard Hobbs19 Sep
      • Re: Query matches twice, but not simultaneously...Michael Stassen19 Sep