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]
