CODEWITHSUNDEEP
javasqloracle-database
touch my body
touch my body

Reputation: 1733

Is it possible to merge these two queries?

I'm writing a sql query to count objects. I have about 50 pre-written queries, and each of them is essentially written like so:

    "select count(1) from SOME_TABLE where identifier = :ID"

I have it in Java. SOME_TABLE is already written into the query (different for each query). At runtime, a switch/case determines which query to execute and which ID to send it.

Essentially, there are various queries which can be called, but ID is the only thing that changes inside the queries.

So, if I want to count all the pinetrees in a forest, I'd send pineTree as an ID to a query that counts Tree. Simple enough. As I mentioned, there's a giant switch/case which determines TABLE_NAME, ID, and the exact query.

The problem is, I'm trying to count all of the objects of a table, and I'm trying to count them with various IDs. For example:

    query1: "select count(1) from LIST_OF_TOYS where animalId = theListOfIDsfromQuery2"
    query2: "select allThePetIDs from LIST_OF_PETS where ownerId = :myID

This is essentially what I'm trying to do. I want to count all the toys owned by my pets, but there is no direct link between my ID and the LIST_OF_TOYS table.

I know how to do this with two separate queries, but I would strongly prefer to write one query that just takes ownerId as a parameter, so that I can add it to my giant switch/case (and keep my code neat). I can write the query however I want, but I was wondering if it's possible to do this as a single query and a single argument. Thanks for the help!

Upvotes: 1

Views: 79

Answers (1)

Yosef Weiner
Yosef Weiner

Reputation: 5751

select count(1) from LIST_OF_TOYS where animalId in (select allThePetIDs from LIST_OF_PETS where ownerId = :myID)

Upvotes: 3

Related Questions