• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using the results of one query as values to iterate through in another query with Groovy SQL

 
Ann Doyle
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. I am currently trying to piece together a way of getting the results from one mysql query, adding them to a list, then iterating through these results in another query using Groovy.
My code is as follows:



I am a bit unsure where to go from here, this does not appear to work, I'm pretty sure the it.queryResults is wrong, but I need to fill this specific bit of the query with each of the results of the previous query in turn.
I also need the list items to appear with quotation marks around them in order for the second query to work, which I am not sure can happen like this.
Any suggestions would be really appreciated.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This looks like a job for SQL GROUP BY. Nesting related SQL queries like this is an anti-pattern, because it usually means you're trying to implement some kind of join or aggregation that would be much more efficient in SQL.

Put Groovy aside for now and open up your database's SQL shell. Then work out how to join your ticket and fields tables in a single query. Finally, modify the query to use GROUP BY and COUNT to produce the list of ticket counts grouped by field. Once you've got the query working in your SQL interpreter, you can put it into your Groovy program.

Use the right tool for the job - and SQL is the right tool for this job.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic