Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 05-29-2007, 07:20 PM
Antivirus's Avatar
Antivirus Antivirus is offline
 
Join Date: Sep 2004
Location: Black Lagoon
Posts: 1,090
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Stumped on really UGLY query...

Ugh... I am working on a query which pulls a lot of data from many different tables. It is as follows:

Code:
$sql = $db->query_read("
   SELECT scst_task.*, scst_tasktype.tasktype, scst_team.teamtitle, scst_feedback.feedbackid
   FROM scst_task
   LEFT JOIN scst_tasktype ON scst_tasktype.tasktypeid = scst_task.tasktypeid
   LEFT JOIN scst_team ON scst_team.teamid = scst_task.teamid
   LEFT JOIN usergroup ON usergroup.scstugteamid = scst_task.teamid
   LEFT JOIN scst_feedback ON scst_feedback.taskid = scst_task.taskid
   WHERE (scst_task.recurring >= (COUNT scst_feedback.feedbackid))
   ORDER BY scst_task.enddate ASC, scst_team.teamtitle ASC
");
The problem is with the WHERE clause. It's not doing what I need. I need it to only pull the tasks associated with feedback quantity which is less than or equal to the value in that task's 'recurring' column.

In other words, the data in the task table could have 2 rows as follows:

taskid1 || recurring 2
taskid2 || recurring 1
taskid3 || recurring 1


... while the data in the feedback table could have 3 rows as follows:

feedbackid1 || taskid 1 || miscdata
feedbackid2 || taskid 2 || miscdata
feedbackid4 || taskid 1 || miscdata
feedbackid5 || taskid 3 || miscdata


...therefore the results would pull only taskid1 since taskid2 & taskid3 have <= occurrences in feedback table with records linked to them.

If i haven't explained this well, please let me know, i'd be happy to go into more detail. Thanks
Reply With Quote
  #2  
Old 05-29-2007, 07:51 PM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Would "GROUP BY scst_feedback.feedbackid" work?
Reply With Quote
  #3  
Old 05-29-2007, 09:15 PM
Antivirus's Avatar
Antivirus Antivirus is offline
 
Join Date: Sep 2004
Location: Black Lagoon
Posts: 1,090
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by harmor19 View Post
Would "GROUP BY scst_feedback.feedbackid" work?
Thanks for suggestion harmor, however unfortunately it doesn't work. I'm trying to fiddle around with that in conjunction with a HAVING clause, but i'm not too familiar with it.

I tried the following, but no luck either...
Code:
SELECT scst_t.*, scst_ttype.ttype, scst_tm.tmtitle, COUNT(scst_fb.fbid) AS fbqty
FROM scst_t
LEFT JOIN scst_ttype ON scst_ttype.ttypeid = scst_t.ttypeid
LEFT JOIN scst_tm ON scst_tm.tmid = scst_t.tmid
LEFT JOIN usergroup ON usergroup.scstugtmid = scst_t.tmid
LEFT JOIN scst_fb ON scst_fb.tid = scst_t.tid
GROUP BY scst_t.tid
HAVING fbqty <= scst_t.recurring
ORDER BY scst_t.enddate ASC, scst_tm.tmtitle ASC
Reply With Quote
  #4  
Old 05-29-2007, 09:38 PM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Are the columns "recurring" (on "scst_task") and "taskid" (on "scst_feedback") related?
I see that "recurring" has the value "2" and there are two taskid's with the same value "feedbackid1 || taskid 1 || miscdata".
Reply With Quote
  #5  
Old 05-29-2007, 09:44 PM
Antivirus's Avatar
Antivirus Antivirus is offline
 
Join Date: Sep 2004
Location: Black Lagoon
Posts: 1,090
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

the records in scst_feedback are related to records in scst_tasks by "taskid". The "recurring" value (within the scst_tasks table) is simply the max amount of records in scst_feedback to allow. If the amount of records in scst_feedback are exceeded, then the related record in scst_task should not be returned. I think i am close with the post above...
Reply With Quote
  #6  
Old 05-29-2007, 09:53 PM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Have you tried echoing $sql['fbqty'] and $sql['recurring'] to see what the values are? Maybe it will help you solve the query.
Reply With Quote
  #7  
Old 05-29-2007, 10:07 PM
Antivirus's Avatar
Antivirus Antivirus is offline
 
Join Date: Sep 2004
Location: Black Lagoon
Posts: 1,090
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Hmm... I'm going to run the query through Navicat, it'll allow me to immediately see result set. I think the Group BY along with COUNT and HaVING are going to work for me.. .just need to be sure...

yup... that does work! (at least when testing with just a few records). My problem was simply the <= operator, really needed to just be <


I'm going to load up the tables with more records and see if results are as expected.

Thanks for tremendous help
Reply With Quote
  #8  
Old 05-29-2007, 10:39 PM
harmor19 harmor19 is offline
 
Join Date: Apr 2005
Posts: 1,324
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I wasn't that much of a help though.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 10:26 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.05424 seconds
  • Memory Usage 2,223KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (8)post_thanks_box
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (8)post_thanks_postbit_info
  • (8)postbit
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.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
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete