How to Consolidate Multiple Queries (1 Viewer)

[ilcaa72]

I have 6 different saved queries. They each query a list of financial Stocks symbols for different metrics.

I wanted to create 1 query that returns a consolidated list of symbols that are in each of the 6 queries, so what Symbols are in each of the lists (hope that makes sense). thanks for any assistance. Let me know if you need more information

Here is a screenshot of the Queries.

https://drive.google.com/open?id=0ByTl_9UVF7ukT0tKcDVrWUpyc2M

 

[The_Doc_Man]

Are those ALL examples of one/one relationships? I don't understand what you posted.

First thought: Everything depends on the same fields, apparently - Symbol and Country (with one case where the order is different, I would guess for sorting differences?). You have absolutely no barrier to writing a query that combines ALL of the returned fields (noting that the different ordering for that one oddball cases might have to be handled separately.)

So... second thought: The criteria rows didn't show up. Is your problem different criteria, differences in the WHERE clauses? Because if not, why would you NOT simply have one query with eight more fields, each taken from the appropriate source? Or do some of these queries have different sources? Tell us why you wouldn't just write a single query as a one-size-fits-nearly-all.

 

[ilcaa72]

Hi Doc,

Yes each individual query has different criteria and I need them individual. The only useful field of each query is the Symbol field. So, what common symbol appears in each query result.

Query1: A, B, C, D, E, F
Query2: B, D, F, G, H, I, R, S
Query3: B, F, G,
Query4: F, G, M, P

What I want is 1 query that should return F for this example. Its the only symbol that appears in each individual query. Im just not sure how to write it. hope that makes sense

 

[The_Doc_Man]

I see. The exampled helped clarify. So your problem is that you are looking for something that shows up at least once in each individual query. But you might not care if it appeared more than once. I might approach this with a multi-layered query.

You show four queries. If you have more or less, this works just as well, as long as you don't exceed 16 queries. Query 1 through 4 might be modified so that you do this:

SELECT DISTINCT StockSymbol FROM MyQuery1 ORDER BY StockSymbol ;

for each of your original queries MyQuery1, MyQuery2, .... That potentially gives you a list of ALL of the symbols that appeared in any of the queries, but only once per query.

The second layer would be a UNION query where you had a contributing clause for each of your individual queries. Using a UNION query is why I mentioned the limit of 16 queries to contribute. A UNION query can only merge 16 source queries.

SELECT StockSymbol FROM Query1D 
UNION 
SELECT StockSymbol FROM Query2D 
UNION
...
SELECT StockSymbol FROM QuerynD ;

where Query1D, etc. represent the "SELECT DISTINCT" queries, one SELECT for each of the SELECT DISTINCT queries.

Now you count the appearances of each symbol in the UNION query.

SELECT StockSymbol, COUNT( StockSymbol ) AS SymbolCount FROM QueryU 
GROUP BY StockSymbol ;

where QueryU is the UNION query and you have to do a GROUP BY in order to be able to include StockSymbol with its count.

Now finish this off with

SELECT StockSymbol FROM Query1C WHERE SymbolCount = 4 ;

where Query1C is the COUNT query and 4 is the number of queries that originally were UNIONed together. If you had more or fewer contributing original queries then adjust this number accordingly.

That gives you the list of symbols that appear in all of your original queries. You can use THIS result if you wish to join with your other queries again to get more information from them about these symbols.

THIS IS NOT the only way to do this. I did it a layer at a time to illustrate the approach but there are other ways that might also work depending on what else you can do with your database and whether you might consider modifying something structurally. The layered method is maybe a bit tedious but doesn't require changing any tables.

 

[ilcaa72]

thanks for taking the time to explain.

For each individual query (6 in total) the Symbols will appear 1x per query so each query has a distinct list never duplicates. But each individual query take from the same pool of stock symbols.

I am a little confused about how to structure the SQL. this returns everything from both tables but removes the duplicates. The duplicates is what I am after, what Symbols does each table share in common. what symbol appear in every table.

SELECT DISTINCT StockSymbol FROM MyQuery1 ORDER BY StockSymbol ;
Union
Select DISTINCT StockSymbol from MyQuery2;

query1: stocks where profit has decreased each year for past 3 years. = F, ABC, XYZ, QOT
query2: stocks where debt > 50% = ABC, QOT, LMN, OP, XYZ
query3: stocks where 53wk return < 10% = ANZ, ABC, MLO, QOT

CombinedQuery: show me stocks that share ALL these attributes = ABC, QOT

(the individual queries are more complicated then that, each has like 6 criteria, im trying to simplify my example)

 

[The_Doc_Man]

There IS another way to approach this...

Build a table with ALL of the possible stock symbols (or at least, all of them that you ever examine). I'm betting that you actually already have such a list for translating things about each symbol, so this isn't a big stretch.

Add six fields of type Yes/No to this Symbols table so that you have the Symbol and six "flags" to tell you what you need to about your six queries.

'    Erase the flags

UPDATE SymbolList SET Flag1 = False, Flag2 = False, ..., Flag6 = False ;

UPDATE SymbolList INNER JOIN MyQuery1 ON SymbolList.Symbol = MyQuery1.Symbol SET Flag1 = True ;
...
UPDATE SymbolList INNER JOIN MyQuery6 ON SymbolList.Symbol = MyQuery6.Symbol SET Flag6 = True;

'  Now look for the ones that show up in each query

SELECT Symbol FROM SymbolList WHERE (Flag1 = True) AND (Flag2 = True) AND ... AND (Flag6 = True) ;

This will give you a list of all symbols that appear in all six of your queries. Won't matter HOW many times each symbol appears, whether or not you have a DISTINCT keyword anywhere in the mix.

This is the "change the structure" approach. I mentioned earlier that there were other ways to skin this cat. This is one of them.

At first glance this design with Flag1, Flag2, etc. might seem to be a denormalized table, but I'm a pragmatist. Doing it via individual COUNT records in a normalized structure can work, but this logic is easier and is present only to optimize the selection process. It is simple, with n+2 queries where n is the number of inidividual queries you are using for your various criteria.

 

[ilcaa72]

hi Doc, thanks for the idea. I havent had time to test, I will tomorrow and give your feedback...