Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

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.
Posts: 2407
Scala Python Oracle Postgres Database Linux
  • 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.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic