Subselects & Non Matching Values... :(

[Anon]

Any idea about how to rewrite this for mySQL?

SELECT topics.topic_id, topics.topic_title, topics.topic_replies
FROM topics
WHERE topic_id NOT IN (SELECT topic_id FROM posts_tracking WHERE user_id = 1);

[[deleted]]

use a LEFT JOIN:

--quote--
If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> select table1.* from table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> where table2.id is NULL;
--end quote--

http://www.mysql.com/doc/J/O/JOIN.html

[Anon]

The suggestion you give me will not work since I need to select only the records that do have user_id = 1 on the posts_tracking table.

Table: topics (topic_id, ....)
Table: posts_tracking (user_id, topic_id, ...)

The target is know which are the topics that are not listed for a certain user.

[[deleted]]

"The suggestion you give me will not work since I need to select only the records that do have user_id = 1 on the posts_tracking table."

so add the WHERE clause...

SELECT *
FROM topics LEFT JOIN posts_tracking
WHERE topics.topic_id=posts_tracking.topic_id
AND topics.topic_id IS NULL
AND posts_tracking.user_id = 1;

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

Well... probably you're suggesting something like...

SELECT *
FROM topics LEFT JOIN posts_tracking ON topics.topic_id=posts_tracking.topic_id
WHERE topics.topic_id IS NULL AND posts_tracking.user_id = 1

but this query will not return anything... already tested before... :-(

[[deleted]]

Did you try that from the command line?

[Anon]

Yes... and it does not return aything... the reason is because the LEFT JOIN is causing a NULL value to the user_id that has no matching recodrs at the other table... then when applying the user_id = 1 filter... returns nothing...

[[deleted]]

Ahem.. cough.. ofcourse.. hehe... should have seen that one...

THen I guess you'll have to SELECT the records for user_id=1 into a temporary table, and LEFT JOIN that with the topics.
ame principle, except the right (temp) table only contains data for user_id=1, so you can leave that out of the where clause.

[Anon]

Temporary table... yes.. already done... but performance?... Oh my Buddha!...

mySQL... cheap and works... but with a lot of limitations... :-( Subselects are really a must when you wanna do some serious stuff... nevermind... we'll have to either migrate to another DB or wait until the mySQL guys allow the subselect thing...

This is not a complain... but things are not as great as people thing on the freeware software...

[[deleted]]

Make sure you use HEAP type tables for the temporary tables, then they are created in memory, which is well.. faster.

"This is not a complain... but things are not as great as people thing on the freeware software... "

MySQL is crap when it comes to serious functionality, it's just not there. I've been shouting that for years.

But all is well on the free software front, because PostgreSQL supports just about everything that the 'big boys' do. www.postgresql.org