Query within a query and use headings

[SPYDERIX]

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:

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:

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:

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

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

 

[vacunita]

The EXISTS statement is just a conditional statement. It does not return any columns from the subquery, merely true or false depending on whether the subquery returns any rows or not.

https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

To do what you want, run the subquery as another column, something like this:

SELECT `DL_Divetype`.`ID`,`DL_Divetype`.`Typename`, (SELECT SUM(`DL_Logbook`.`Divetime`) AS `totaltime` FROM `DL_Logbook` WHERE FIND_IN_SET(`DL_Divetype`.`ID`, `DL_Logbook`.`Divetype`) 
) AS Divetime, (SELECT COUNT(*) AS `totaldives` FROM `DL_Logbook` WHERE FIND_IN_SET(`DL_Divetype`.`ID`, `DL_Logbook`.`Divetype`) 
) FROM `DL_Divetype` ORDER BY `Typename` ASC LIMIT 0,100

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[SPYDERIX]

Groovy thanks!

NATE