What is better: One big SQL query or several?

It’s difficult without examples. But what you describe seems like a simple join.

At least as an intermediate step, it may be best to nest them; have a few small queries and a “meta-query” that unifies them. That makes debugging easier. Once you get all the small parts working, then unify them. Make the parts, then assemble them.

I find a modular approach makes it easier to build and maintain than “spaghetti code”.

Yes, an example would be listing product names, with their buyers and the prices (I say this as a close example, otherwise I would have to explain a lot). I made the query joining tables; but what if I have another table with, say, the stores that have those products; then what would be best; add it to the query that already has a join? so far I came up with this, as a rough and quick solution:

<table>(1st joined query)while…<tr><td>xxx</td><td>yyy</td><td>(second joined query using the ID as variable from the first query)fetch…zzz</td></tr>...end while</table>

Doesn’t it depend on the output you want? What kind of report do you want? Where are you going with all this? Are you counting sales by store and product and price? Are you summing sales by store and product? Do you want to know average price per product per store?

My suggestion is that you look at the final product or products and then build your joins in order to enable those final products. Look at your goal. In academic terms, your research question. Everything else is designed in order to meet those requirements.

In a situation like this you need clarity and in my world, the research question provides the clarity. I suspect the business world isn’t all that different.

It doesn’t appear that you’re doing complicated analysis… but I do and whenever I try to make one big query it takes FOREVER to run, so I tend to do several small queries.

Several. One single one is a bitch to debug.

thanks! so i was on a good path after all :)