Hi there,
I have a db table that is for activities that uses unique ID numbers and another table that references those ID numbers but in a comma separated format. I'm working within a framework that is setup like this from a proprietary system so can't change this format.
I want to do a query within a query if possible. I realize I could do my first query then simply do more queries in a loop to achieve what I want however I'm trying to see if this can all be achieved in one shot instead to avoid hammering the server.
First query is:
This is easy enough as it gets my whole list of activities. What I'm trying to do is a stats view to see how many times these activities are done as a total count plus the amount of time from another logbook table with 100's of entries.
My second query simply does the count and sum and gives me exactly what I need per activity:
What I want to be able to do is combine them and so far I have this query:
The problem is that it only shows two headings: ID and Typename. When I try to put in the headings from my "AS" statements it says they don't exist. I feel like I'm close to doing what I want but need the rest to show up. What am I doing wrong here? Surely it should be able to pass the "AS" identifiers to the first query somehow. It's done the query successfully, now how do I show it?
Thanks
NATE
I have a db table that is for activities that uses unique ID numbers and another table that references those ID numbers but in a comma separated format. I'm working within a framework that is setup like this from a proprietary system so can't change this format.
I want to do a query within a query if possible. I realize I could do my first query then simply do more queries in a loop to achieve what I want however I'm trying to see if this can all be achieved in one shot instead to avoid hammering the server.
First query is:
Code:
SELECT `DL_Divetype`.`ID`,`DL_Divetype`.`Typename` FROM `DL_Divetype` ORDER BY `Typename` ASC LIMIT 0,100
This is easy enough as it gets my whole list of activities. What I'm trying to do is a stats view to see how many times these activities are done as a total count plus the amount of time from another logbook table with 100's of entries.
My second query simply does the count and sum and gives me exactly what I need per activity:
Code:
SELECT SUM(`DL_Logbook`.`Divetime`) AS `totaltime`,COUNT(*) AS `totaldives` FROM `DL_Logbook` WHERE FIND_IN_SET(46, `DL_Logbook`.`Divetype`)
What I want to be able to do is combine them and so far I have this query:
Code:
SELECT `DL_Divetype`.`ID`,`DL_Divetype`.`Typename` FROM `DL_Divetype` WHERE EXISTS (SELECT SUM(`Divetime`) AS `totaltime`,COUNT(*) AS `totaldives` FROM `DL_Logbook` WHERE FIND_IN_SET(`DL_Divetype`.`ID`, `DL_Logbook`.`Divetype`)) ORDER BY `DL_Divetype`.`Typename` ASC LIMIT 0,100
Thanks
NATE