Excluding forums in SQL query

[kgroneman]

I need some help with an SQL query. I'm trying to run a query to list the top posters but exclude some specific forums using vb_forumid, but it's not recognized. Here's the query BEFORE trying to exclude forums that works fine:

SELECT count(vb_post.postid) AS vb_postcount, vb_user.username
FROM vb_post AS vb_post
LEFT JOIN vb_user AS vb_user ON (vb_user.userid = vb_post.userid)
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) 
GROUP BY vb_post.userid
ORDER BY vb_postcount
DESC

When I add the forum IDs to exclude, I get the following error:

error number: 1054 
error desc: Unknown column 'vb_forumid' in 'where clause'

Here is the query that produces the error:

SELECT count(vb_post.postid) AS vb_postcount, vb_user.username
FROM vb_post AS vb_post
LEFT JOIN vb_user AS vb_user ON (vb_user.userid = vb_post.userid)
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
AND vb_forumid NOT IN  (10,588,589,591,592,593,594,595,597,599,601,608,609,641,644,645,646,647,649,650,652,654,671,689,737,739,740,741,782,783,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1239,1240,1241,1242,1274,1275,1278,1279,1280,1289,1290,1292)
GROUP BY vb_post.userid
ORDER BY vb_postcount
DESC

I have another forum where I don't use the vb_ prefix (so it's just forumid, not vb_forumid) and the query runs fine so I don't get why this doesn't work on my prefixed forum. Help?

[Dave]

Column names aren't prefixed, only table names are.
Change vb_forumid to forumid.

[kgroneman]

Thanks Dave, that's what I thought too, but when I use just forumid I get the same error:

Message
An error occurred while attempting to execute your query. The following information was returned. 
error number: 1054 
error desc: Unknown column 'forumid' in 'where clause'

Any other ideas? Oh interesting...on a different query, forumid works. I wonder why not on this query? Off to try some other things but if you have any ideas, they would be most appreciated.

[Dave]

The post table does not contain a column called forumid, you'd have to LEFT JOIN the thread table.
So add LEFT JOIN vb_thread ON vb_thread.threadid = vb_post.threadid and change forumid to vb_thread.forumid.

[kgroneman]

First of all THANK YOU so much for your time and effort helping me. I don't know much of anything about SQL queries. I tried your suggestion and got a timestamp error, so I changed the syntax of the date line, with the same error:

SELECT count(vb_post.postid) AS vb_postcount, vb_user.username
FROM vb_post AS vb_post
LEFT JOIN vb_user AS vb_user ON (vb_user.userid = vb_post.userid)
LEFT JOIN vb_thread AS vb_thread ON (vb_thread.threadid = vb_post.threadid) 
WHERE dateline > UNIX_TIMESTAMP('2016-03-31') AND vb_post.dateline < UNIX_TIMESTAMP('2016-05-01')
AND vb_thread.forumid NOT IN (10,588,589,591,592,593,594,595,597,599,601,608,609,641,644,645,646,647,649,650,652,654,671,689,737,739,740,741,782,783,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1239,1240,1241,1242,1274,1275,1278,1279,1280,1289,1290,1292)
GROUP BY vb_post.userid
ORDER BY vb_postcount
DESC

The error is:

An error occurred while attempting to execute your query. The following information was returned. 
error number: 1052 
error desc: Column 'dateline' in where clause is ambiguous

So for some reason, making the changes you suggested changed the way it reads the date, and I'm not nearly smart enough to figure out why. :-\

[squidsk]

Originally Posted by kgroneman

First of all THANK YOU so much for your time and effort helping me. I don't know much of anything about SQL queries. I tried your suggestion and got a timestamp error, so I changed the syntax of the date line, with the same error:

SELECT count(vb_post.postid) AS vb_postcount, vb_user.username
FROM vb_post AS vb_post
LEFT JOIN vb_user AS vb_user ON (vb_user.userid = vb_post.userid)
LEFT JOIN vb_thread AS vb_thread ON (vb_thread.threadid = vb_post.threadid) 
WHERE dateline > UNIX_TIMESTAMP('2016-03-31') AND vb_post.dateline < UNIX_TIMESTAMP('2016-05-01')
AND vb_thread.forumid NOT IN (10,588,589,591,592,593,594,595,597,599,601,608,609,641,644,645,646,647,649,650,652,654,671,689,737,739,740,741,782,783,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1239,1240,1241,1242,1274,1275,1278,1279,1280,1289,1290,1292)
GROUP BY vb_post.userid
ORDER BY vb_postcount
DESC

The error is:

An error occurred while attempting to execute your query. The following information was returned. 
error number: 1052 
error desc: Column 'dateline' in where clause is ambiguous

So for some reason, making the changes you suggested changed the way it reads the date, and I'm not nearly smart enough to figure out why. :-\

When two tables have columns with the same name and you want to refer to one of them you need to prefix it with the table name. In your case both thread and post have a dateline column so you'd need to use post.dateline if you wanted the post tables dateline column or thread.dateline if you wanted the thread table dateline column.

[kgroneman]

That did it! Thanks to you both! This now works.

SELECT count(vb_post.postid) AS vb_postcount, vb_user.username
FROM vb_post AS vb_post
LEFT JOIN vb_user AS vb_user ON (vb_user.userid = vb_post.userid)
LEFT JOIN vb_thread AS vb_thread ON (vb_thread.threadid = vb_post.threadid) 
WHERE vb_post.dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
AND vb_thread.forumid NOT IN (10,588,589,591,592,593,594,595,597,599,601,608,609,641,644,645,646,647,649,650,652,654,671,689,737,739,740,741,782,783,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1239,1240,1241,1242,1274,1275,1278,1279,1280,1289,1290,1292)
GROUP BY vb_post.userid
ORDER BY vb_postcount
DESC

I owe you guys...and so fast on the replies! I'm amazed and thankful.