The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
||||
|
||||
Excluding forums in SQL query
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:
Code:
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 HTML Code:
error number: 1054 error desc: Unknown column 'vb_forumid' in 'where clause' Code:
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 |
#2
|
|||
|
|||
Column names aren't prefixed, only table names are.
Change vb_forumid to forumid. |
#3
|
||||
|
||||
Thanks Dave, that's what I thought too, but when I use just forumid I get the same error:
HTML Code:
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' |
#4
|
|||
|
|||
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. |
2 благодарности(ей) от: | ||
kgroneman, TheLastSuperman |
#5
|
||||
|
||||
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:
Code:
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 HTML Code:
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 |
#6
|
||||
|
||||
Quote:
|
2 благодарности(ей) от: | ||
kgroneman, TheLastSuperman |
#7
|
||||
|
||||
That did it! Thanks to you both! This now works.
Code:
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 |
Благодарность от: | ||
TheLastSuperman |
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|