Two queries into one (1 Viewer)

ponneri · Sep 23, 2017

Hello.

I have a table, on which I have two queries with different conditions - that result in the same resulting columns.

Is there a way to combine these two queries so that it appears as one resultant set of records ?

As of now, I run two queries, then export to excel twice and copy/paste one set below the other with a label to distinguish them

Please help.

arnelgp · Sep 23, 2017

just create another query (SQL View)

SELECT Query1.CommonColumn As Column1, Query2.CommonColumn As Column2 FROM Qeuery1, Query2

Pat Hartman · Sep 23, 2017

Sounds like you need a Union query

Select * from query1
Union Select * from query2;

This will "stack" the recordsets one after the other.

The other suggestion will create a Cartesian Product which essentially multiplies query 1 by query 2 so you end up with a recordset that joins every row from table 1 with every row from table 2. So if tbl1 has 1000 rows and tbl2 has 1000 rows, the resulting recordset will contain 1 million rows. The recordset of the union will be 2000 rows.

ponneri · Sep 23, 2017

Thank you.

You are right. My idea is to get the stack of rows one below the other !

And thanks for clarifying on the other suggestion too.

Appreciate it.

Pat Hartman said:
Sounds like you need a Union query

Select * from query1
Union Select * from query2;

This will "stack" the recordsets one after the other.

The other suggestion will create a Cartesian Product which essentially multiplies query 1 by query 2 so you end up with a recordset that joins every row from table 1 with every row from table 2. So if tbl1 has 1000 rows and tbl2 has 1000 rows, the resulting recordset will contain 1 million rows. The recordset of the union will be 2000 rows.

Pat Hartman · Sep 25, 2017

You're welcome.

MarkK · Sep 25, 2017

It's also possible in my mind, from what you wrote, that you could just re-write your where clause.
Maybe you have two queries like this...

Code:

1. SELECT * FROM Table WHERE ID = 12
2. SELECT * FROM Table WHERE ID = 13

Is that what you mean by "two queries with different conditions?"

If so, it might be possible to combine those conditions in one query and write something like...

Code:

SELECT * FROM Table WHERE ID = 12 OR ID = 13

This would simpler than a UNION query, which requires you to maintain three different queries.
hth
Mark

Stanski21 · Sep 27, 2017

As Markk says, depends on what sort of relationship your second query has to your first as to whether a UNION query would be needed.
But you could always write out the UNION query in full so only 1 query needs to be maintained.

I like UNION queries when the second query summarises the first query grouped at a higher level e.g. I'd have individual performance metrics in the first query, then group them by team level in the second

But if you're just taking the same data from the table twice (but for two different sets of records), an update to the WHERE clause would be enough

Two queries into one (1 Viewer)

ponneri

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

ponneri

Registered User.

Pat Hartman

Super Moderator

MarkK

bit cruncher

Stanski21

Registered User.

Similar threads

Users who are viewing this thread