query calling too many fields

[spyke01]

ok, im trying to run this query:

SELECT t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username from `topics` t, `users` u , `posts_read` pr WHERE (pr.pr_userid = '1' AND pr.pr_topic_id != t.topic_id AND u.users_userid = t.topic_poster)

 

basiclly it should do this:

get these fields for any topic that hasnt been read

 

whenever a topic is read, the user's id and the topic id go into the posts_read table that way we can track them, whenever i run this query i get over 2,000 results! theres only 120 posts all together and thats posts not topics!

 

when i run the query, i find that its pulling the same rows over and over, like if i organize the results by t.topic_id i get hundreds of rows that have a 1 for t.topic_id, the only tables being checked here that have topic_id in them is posts_read and topics heres the table schemes:

 

posts_read:

CREATE TABLE `posts_read` (
 `pr_id` mediumint(8) NOT NULL auto_increment,
 `pr_topic_id` mediumint(8) NOT NULL default '0',
 `pr_userid` mediumint(8) NOT NULL default '0',
 PRIMARY KEY  (`pr_id`)
) TYPE=MyISAM AUTO_INCREMENT=268;

 

topics:

CREATE TABLE `topics` (
 `topic_id` mediumint(8) NOT NULL auto_increment,
 `topic_forum_id` smallint(8) NOT NULL default '0',
 `topic_title` varchar(60) NOT NULL default '',
 `topic_time` int(11) NOT NULL default '0',
 `topic_poster` mediumint(8) NOT NULL default '0',
 `topic_views` mediumint(8) unsigned NOT NULL default '0',
 `topic_replies` mediumint(8) unsigned NOT NULL default '0',
 `topic_type` tinyint(3) unsigned NOT NULL default '0',
 `topic_status` tinyint(3) NOT NULL default '0',
 `topic_first_post_id` mediumint(8) unsigned NOT NULL default '0',
 PRIMARY KEY  (`topic_id`)
) TYPE=MyISAM AUTO_INCREMENT=98;

 

if need be, i can give you some things to insert into these tables, if someone can please help me itd be great

[ryanlwh]

you're not joining the tables correctly. also, when needing to return something not existing in a certain table, it's best to do with LEFT JOIN or RIGHT JOIN.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username FROM `users` u LEFT JOIN (`topics` t, `posts_read` pr) ON (u.users_userid = t.topic_poster, t.topic_id = pr.pr_topic_id) WHERE pr.pr_userid = '1' AND pr.pr_id IS NULL [!--sql2--][/div][!--sql3--]

Untested...

[spyke01]

hmm, i get this error:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ' t . topic_id = pr . pr_topic_id ) WHERE pr . pr_userid = '1' A

 

can you give me a quick explanation of how join works?

[ryanlwh]

hmm, maybe you're using an older version of mysql?? change it to LEFT JOIN table ON condition LEFT JOIN table ON condition instead.

 

take a look at this page from mysql.com for join syntaxes http://dev.mysql.com/doc/refman/5.0/en/join.html

 

this page has a good explanation of how joins work

http://www.wellho.net/mouth/158_MySQL-LEFT...OUTER-JOIN.html

 

[spyke01]

ok, im using 4.0.25

 

heres what i tried:

SELECT t . topic_id , t . topic_status , t . topic_type , t . topic_title , t . topic_status , t . topic_poster , t . topic_views , t . topic_replies , u . users_username FROM `users` u LEFT JOIN `topics` t ON u . users_userid =t . topic_poster LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_userid ='1' AND pr.pr_id IS NULL ORDER BY `t` . `topic_id` ASC

 

and this:

SELECT t . topic_id , t . topic_status , t . topic_type , t . topic_title , t . topic_status , t . topic_poster , t . topic_views , t . topic_replies , u . users_username FROM `users` u LEFT JOIN `topics` t ON u . users_userid =t . topic_poster LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_userid ='1' ORDER BY `t` . `topic_id` ASC

 

the first one does nothing but succed, and the second one pulls up all the topics in the database, any ideas?

[ryanlwh]

try putting the IS NULL into the second one.

[spyke01]

hmm still not working, i think im going to have to run 2 querys, 1 to get the topics, and the other to check if theyre read or not

 

i also relized, that we cant compare anything with posts_read, because the only way there is something in there with a topic_id matching the one in topics is if the topic has been read, and we want all the ones NOT read

 

so we need a query that says:

select all the topics that dont have an id in posts_read for this userid

[ryanlwh]

i think i was checking for the wrong column. joins are tricky especially when dealing with non-existence. from the mysql manual, IS NULL is how you pull rows that doesn't exists:

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;

 

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL.

 

yeah try to split it into two and see if you could get it, then combine them. maybe easier to go that way.

[spyke01]

ok, i know how ill do it for 2 querys, but this is annoying me, i tried this also:

 

SELECT * FROM `topics` t LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_id IS NULL

 

and its still a no go

[ryanlwh]

try IS NULL on pr.pr_topic_id instead

[spyke01]

nope, its still not going, gives me no rows

[spyke01]

*bump

[shoz]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]

t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username

FROM `topics` t

INNER JOIN `users` u

ON u.users_userid = t.topic_poster

LEFT JOIN `posts_read` pr

ON pr.pr_topic_id = t.topic_id AND pr.pr_userid = 1

WHERE ISNULL(pr.pr_topic_id) [!--sql2--][/div][!--sql3--]

If the query above does not work, post the create statement for the users table and perhaps a small dump with about 5 topics 2 posts read and a couple of users.

 

EDIT: Test the query by itself first using an interface such as Phpmyadmin or the default mysql client "mysql monitor"