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

-- 
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