vb.org Archive - Stumped on really UGLY query...

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Stumped on really UGLY query... (https://vborg.vbsupport.ru/showthread.php?t=148423)

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 :o

Would "GROUP BY scst_feedback.feedbackid" work?

Quote:

Originally Posted by harmor19 (Post 1257087)

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

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".

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...

Have you tried echoing $sql['fbqty'] and $sql['recurring'] to see what the values are? Maybe it will help you solve the query.

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 :)

I wasn't that much of a help though.

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (8)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
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01036 seconds Memory Usage 1,733KB 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 (2)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (8)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
Messages: