Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query within a query and use headings

Status
Not open for further replies.

SPYDERIX

Technical User
Jan 11, 2002
1,899
CA
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:
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
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
 
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.

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

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top