simple select query

[dave09]

ok, maybe not that simple...

MYSQL 5.0.58

 

it's basically for use on ipb but I figured I might get a better answer here.

 

2 tables

 

ibf_posts

author_idtopic_id

11000

11001

21001

21002

 

ibf_topics

tid

1000

1001

1002

 

return  "SELECT DISTINCT(p.author_id), t.* 
    				FROM ibf_topics t
			  		LEFT JOIN ibf_posts p ON (p.topic_id=t.tid )
			 	WHERE p.author_id=1
			 	";

 

this gets the inverse of what I want.

if I do p.author_id!=1 ... it picks up the 2 1001 which I don't want.

 

In the table above, I only want tid 1002 returned for author_id 1.

 

any ideas?

[fenway]

Try this:

SELECT t.tid
FROM ibf_topics AS t
LEFT JOIN ibf_posts AS p ON ( p.topic_id=t.tid AND p.author_id=1 )
WHERE p.topic_id IS NULL