×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Jobs

Query within a query and use headings

Query within a query and use headings

Query within a query and use headings

(OP)
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 smile

NATE

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.
https://dev.mysql.com/doc/refman/5.7/en/exists-and...

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

RE: Query within a query and use headings

(OP)
Groovy thanks!

NATE

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!

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