vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)
-   -   Excluding forums in SQL query (https://vborg.vbsupport.ru/showthread.php?t=322616)

kgroneman 05-12-2016 12:35 PM
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
When I add the forum IDs to exclude, I get the following error:
HTML Code:
error number: 1054
error desc: Unknown column 'vb_forumid' in 'where clause'
Here is the query that produces the 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)
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 05-12-2016 12:59 PM
Column names aren't prefixed, only table names are.
Change vb_forumid to forumid.

kgroneman 05-12-2016 01:11 PM
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'
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 05-12-2016 01:20 PM
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 05-12-2016 01:40 PM
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
The error is:
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
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 05-12-2016 01:43 PM
Quote:
Originally Posted by kgroneman (Post 2570619)
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
The error is:
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
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 05-12-2016 01:48 PM
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
I owe you guys...and so fast on the replies! I'm amazed and thankful.


All times are GMT. The time now is 03:59 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.00967 seconds
  • Memory Usage 1,738KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (5)bbcode_code_printable
  • (4)bbcode_html_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (7)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete