Stumped on really UGLY query...

[Antivirus]

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


$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

[harmor19]

Would "GROUP BY scst_feedback.feedbackid" work?

[Antivirus]

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

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

[harmor19]

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

[Antivirus]

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

[harmor19]

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

[Antivirus]

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

[harmor19]

I wasn't that much of a help though.