Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Query within a query and use headings

Query within a query and use headings

Query within a query and use headings

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 smile


RE: Query within a query and use headings

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:


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

RE: Query within a query and use headings

Groovy thanks!


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close