List:General Discussion« Previous MessageNext Message »
From:Richard Hobbs Date:September 19 2005 10:15am
Subject:Re: Query matches twice, but not simultaneously...
View as plain text  
Hello,

Perfect :-)

Thank you,
Hobbs.


Quoting Peter Brawley <peter.brawley@stripped>:

> 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/mysql?unsub=1
>
>



-- 
Richard Hobbs
hobbs@stripped

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

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