Query Help Please! Trying to limit select records from 1 table based on another.

[turboterror]

Hi all!

Long time reader of the forums, first time poster!

 

First table design:

Topics table:

 

id (int 10)

topic_id (int 10)

topic_title (tinytext)

 

Replies table:

topic_id (int 10)

reply_id (int 10)

posters_name (varchar 255)

 

I have a problem, I am trying to select all topics from the Topics table where there has been no reply. When a reply is made, the topic_id is inserted into the Replies table. So if I replied to topic id #10, the replies table would now include a record with the topic_id #10, the id to the reply (not important) and the name of the poster who replied (posters_name). Pretty simple design.

 

Let's assume I am doing this query for the poster named "timmy". My question is this, how do I select all rows from Topics where timmy hasn't replied to that topic? I've tried queries such as:

 

$query = mysql_query("SELECT DISTINCT * FROM Topics JOIN Replies ON Topics.topic_id != Replies.topic_id AND Replies.posters_name = 'timmy' LIMIT 0,1");

 

This query would work great if I removed the AND condition (Replies.posters_name = 'timmy') and wasn't picky about who made the reply. Since I want to select only those rows where timmy didn't reply, I figured the AND condition would work, but it falls on it's face miserably.

 

Please help me out, I would really appreciate it!

 

James

[turboterror]

Please does anyone know how to solve this?!

 

I am very grateful if you would please contribute!

 

Thank you!

[Illusion]

SELECT DISTINCT * FROM Topics JOIN Replies ON Topics.topic_id = Replies.topic_id AND Replies.posters_name != 'timmy' LIMIT 0,1

[turboterror]

Hi Illusion,

Thanks for the suggestion but that will not work because I am looking for topic_id's within Topic which are NOT in Replies. Your query was unable to find any matches as there were no Replies, instead it should found 100% of the Topic's table as there is no Replies in the test db right now.

 

Any other suggestions?

 

Thanks!

[turboterror]

I just had a thought, perhaps the new MYSQL Subquery functionality would work? I have tried, but was unsuccessful in drafting a Subquery query. (tongue twister eh?)

 

Please, if anyone can solve this I would greatly appreciate it!

 

[phpwonderkid]

"When a reply is made, the topic_id is inserted into the Replies table" ----

and u r also checking this condition Replies.posters_name = 'timmy'

You mentioned in the previous post that the test DB for replies table is empty so the condition to check for the posters name would fail and the query will be empty.

[ashishmat1979]

Check this query if it solves your purpose:

 

$query = mysql_query("SELECT DISTINCT Topics.topic_id FROM Topics LEFT JOIN Replies ON Topics.topic_id = Replies.topic_id WHERE Replies.posters_name != 'timmy' ");