Comibining 2 different Query's to get combined result

Graeme
Graeme used Ask the Experts™
on
Hi there, I have 2 querys I would like to join to get a combined result.

Query 1:
SELECT * FROM events,codes WHERE event_code=code_short AND event_IATA='"&Response.QueryString("location")&"' ORDER BY event_start

Open in new window

Query 2:
SELECT * FROM itins,itin_deps WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depdate='"&Response.QueryString("location")&"' ORDER BY itin_depdate

Open in new window


Unfortunately I have no idea how to do this and Google searching for examples hasnt been fruitful (havent found any examples that look remotely correct)

So somehow I would like to join these and have 1 continuous outcome ORDER BY their date fields
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
In order to join these 2 queries you need to exact number of columns with exactly the same name and UNION them
SELECT col1,col2
FROM TableA
UNION
Select col1, col2
FROM TableB

Open in new window

In case TableB does has different names in the columns we have
SELECT col1,col2
FROM TableA
UNION
Select col3 as col1, col4 as col2
FROM TableB

Open in new window

Similar if we are missing a column
SELECT col1,col2,col7
FROM TableA
UNION
Select col1, col2,1 as Col7
FROM TableB

Open in new window

Graemewebber4technologies

Author

Commented:
Hi John, this looks very useful! :)

So to get this right...
SELECT event_IATA,event_code,event_start,code_short
FROM events,codes
UNION
SELECT itins.itin_ID,itin_deps.itin_ID,itin_depcity AS event_IATA,itin_depdate AS event_start
FROM itins,itin_deps
WHERE event_code=code_short AND itins.itin_ID=itin_deps.itin_ID AND itin_depcity='"&Request.QueryString("location")&"' AND event_IATA='"&Request.QueryString("location")&"'

Open in new window

??
John TsioumprisSoftware & Systems Engineer

Commented:
Just match the name of the columns and you are good to go...
e.g
itins.itin_ID  as event_IATA

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You don't have to match the names of the columns, but both sides of the UNION must contain the same number of columns (yours does not top has 4 , bottom has 3) and the data type of corresponding columns must match.

Use of the UNION syntax will eliminate any duplicate values in the group of columns you retrieve.  When I want to include possible duplicates you should use the UNION ALL syntax, and whenever I do that, I include an additional column [Source] to help me define which part of the union they both each came from, something like:

SELECT 1 as [Source], [Field1_long], [Field2_long], [Field3_date] FROM query1
UNION ALL
SELECT 2 as [Source], [SomeField_long], [AnotherField_long], [SomeDateField] from query 2

Note: same number of columns and date types of each corresponding column must match.
Graemewebber4technologies

Author

Commented:
Hi Dale, cheers for that I have matched it up

SELECT event_IATA,event_start
FROM events
UNION ALL
SELECT itin_depcity AS event_IATA,itin_depdate AS event_start
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='"&Request.QueryString("location")&"' AND event_IATA='"&Request.QueryString("location")&"'")

Open in new window

The output of these would be 2 different items - itin_ID & event_ID
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
@Graeme,

"The output of these would be 2 different items - itin_ID & event_ID "

Is that a statement or a question?  Does it do what you are looking for?
Graemewebber4technologies

Author

Commented:
This is the error I get from the statement:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

Open in new window


What I want from it is - event_ID (from the events side) and itin_ID (from the itin side)
Distinguished Expert 2017
Commented:
don't alias the columns from the second query.  It isn't necessary.   As Dale already mentioned, the UNION doesn't map fields by name, it maps them by position and it expects the columns in position 1 to be the same data type and the columns in position 2 to be the same data type, etc.

You also need to fix your join and your selection criteria.  You were referring to something called "Request.QueryString" but that isn't coming from any object in the query.

SELECT event_IATA, event_start
FROM events
WHERE event_IATA = [EnterLocation]
UNION ALL
SELECT itin_depcity, itin_depdate
FROM itins Inner Join itin_depcity ON itins.itin_ID = itin_deps.itin_ID
WHERE itin_depcity = [EnterLocation[
Big MontyWeb Ninja at large

Commented:
the error you're getting is telling you one of the values you're passing into your query is empty. I assume you're using the command object to get your data? One of the parameters you're passing into it has no value.

the sql in this comment, if you hard code the values and run it directly in your DB, do you get the expected results?
Distinguished Expert 2017

Commented:
I tried to edit my reply but Big Monty was submitting and so, here's the edit -- the ending bracket was incorrect.

WHERE itin_depcity = [EnterLocation]

Since both queries are asking for [EnterLocation], you will only get one prompt.  You could also reference a form control which would be better than prompting.

WHERE somefield = Forms!yourform!somecontrolname
Graemewebber4technologies

Author

Commented:
Hi Pat, so you are saying I can either choose:

WHERE itin_depcity= [EnterLcoation]

-or-

WHERE event_IATA= [EnterLocation]

Not both?

Both is important as they define what I require

I know the Request.QueryString("location") definately works as the page I am using the Query on, displays when I comment out the line (we are trying to build) and a different query works with that data.
Distinguished Expert 2017

Commented:
Each where clause is applied separately.  One to one table and the other to the other table.  That restricts all the records to be the same location.  You had criteria for two tables in the same query and that query didn't have both tables.

In a union query, each select is essentially a separate query.  Each query is run independently of the others and then the result set is returned of all the selected tables from every query.
Graemewebber4technologies

Author

Commented:
Sorry I am finding this very hard to follow...

So I can do both WHERE clauses?
Distinguished Expert 2017

Commented:
EACH query in the union should have its own where clause if that is what you mean.

Select tblStudents.* From tblStudents WHERE State = "CT"
Union Select tblStudents.* From tblStudents WHERE State = "MA"

Gives you a recordset containing only students from CT or MA.  The first query selects CT students and the second selects "MA" students.  Of course you wouldn't do this as a union but it should make it clear that each query within the union has its own WHERE clause.
Graemewebber4technologies

Author

Commented:
Ah that makes much more sense, thank you

I will give that a go
Graemewebber4technologies

Author

Commented:
like this:

SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()

Open in new window

Ryan ChongSoftware Team Lead

Commented:
Ok, so based on your last SQL statement in comment: ID: 42050510 , is there anything else you want to achieve? or did you get any error by running it?
Graemewebber4technologies

Author

Commented:
the end result of this is either an event_ID or itin_ID

from this i need to somehow identify what is an event_ID and whats an itin_ID

then I can do a separate query to get more details from it within the loop

eg: http://dev.gptouring.com.au/?location=ESGPA how this is ONLY events as an example
Ryan ChongSoftware Team Lead

Commented:
the end result of this is either an event_ID or itin_ID

from this i need to somehow identify what is an event_ID and whats an itin_ID
So what's the logic behind of it?

What I want from it is - event_ID (from the events side) and itin_ID (from the itin side)
do you need something like this?
select tableA.event_ID , tableB.itin_ID ...
OR some results that generated using the Union clause?
Graemewebber4technologies

Author

Commented:
the logic behind it is...

From a particular location I want to display events and itineraries in date order

for tableA.event_ID, tableB.itin_ID v UNION i am not so sure to tell you the truth

All I know is that I need some of the variables (date>Now(), live=true and location=LOCATION) to determine which itineries and events should be shown
Ryan ChongSoftware Team Lead
Commented:
From a particular location I want to display events and itineraries in date order
ok, so you may try something like this:

SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
Order By 2

Open in new window


OR
Select *
FROM
(
SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
) As a
Order By event_start

Open in new window

Ryan ChongSoftware Team Lead
Commented:
for first example, you can also use:

SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
Order By event_start

Open in new window

this should work as well.
Graemewebber4technologies

Author

Commented:
the ORDER BY event_start will also do ORDER BY itin_depdate ??
Ryan ChongSoftware Team Lead

Commented:
the ORDER BY event_start will also do ORDER BY itin_depdate ??
yes, it will. if you not confident enough, you can also aliases the field name to be identical if necessary.

check some of the examples in these links:

SQL: UNION ALL Operator
https://www.techonthenet.com/sql/union_all.php

UNION Query Operator
http://sourcedaddy.com/ms-access/union-query-operator.html
Graemewebber4technologies

Author

Commented:
OK, have tried both:
SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
Order By event_start

Open in new window

Select *
FROM
(
SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
) As a
Order By event_start

Open in new window


error:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

Open in new window

Ryan ChongSoftware Team Lead

Commented:
No value given for one or more required parameters.
are you spell the field names correctly?

what if you run the queries separately, will you get any error?

SELECT event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()

Open in new window

and
SELECT itin_depcity,itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()

Open in new window

Graemewebber4technologies

Author

Commented:
Argh! found 1 table mispelt

itin_depcity supposed to be itin_deps

changing this over, now get this error:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
This event_start > Now() troubles me...shouldn't be event_start < Now()
Graemewebber4technologies

Author

Commented:
Oh! Hang on... its working, the code below is erroring... (without the change from > to <)

The Next thing is I need to identify if its an event._ID or an itin_ID

So I can run the full query on the product
Ryan ChongSoftware Team Lead

Commented:
Item cannot be found in the collection corresponding to the requested name or ordinal.
yea, check the field names as well, make sure they spelled correctly.

its only showing the events(2), not the itins(2) - 4 total

http://dev.gptouring.com.au/?location=AUGPA
can you highlighted the current and your expected results?
Graemewebber4technologies

Author

Commented:
sorry, I just removed the last post, that was an error by me

sorry...
Distinguished Expert 2017
Commented:
1. Now() is the current date and time.  This could cause confusion and erroneous results if what you are trying to compare to is Date() which is simply the current date.
2. Use the current join syntax I posted earlier.  Non-specific joins such as you are using always result in Cartesian Products and prevent the query engine from optimizing its process.  The result ends up being the same set of records because the where clause whittles down the returned rows but you are making the query engine work harder than it needs to.
3. If you need to distinguish which source generated a particular row in a union query, include a calculated field.

SELECT "Event" As TypeCD, event_IATA, event_start
FROM events
WHERE event_IATA = [EnterLocation]
UNION ALL
SELECT "itinerary" As TypeCD, itin_depcity, itin_depdate
FROM itins Inner Join itin_depcity ON itins.itin_ID = itin_deps.itin_ID
WHERE itin_depcity = [EnterLocation]
Order by event_start, 1;

The resultset takes the column names from the first query.
Graemewebber4technologies

Author

Commented:
Hi Ryan, sorry didnt see the below

Currently I have it showing how many results

The expected results I would like is to show the event_ID's and itin_ID's and define which is which so I can then produce the query for all the information
Graemewebber4technologies

Author

Commented:
Thanks for that Pat, currently the Query example that Ryan has give works without error - so I am going to stick with this now.

However point 3 intrigues me, what do you mean 'include a calculated field'?
Ryan ChongSoftware Team Lead
Commented:
3. If you need to distinguish which source generated a particular row in a union query, include a calculated field.
I think what Pat's mean is to include the source field, so that we will know that particular is from first query or second query.

for example:

Select *
FROM
(
SELECT 1 as src, event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT 2 as src, itin_depcity, itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
) As a
Order By event_start

Open in new window


Look for the derived/ calculated field: src
Graemewebber4technologies

Author

Commented:
Just tried that code, error:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

Open in new window

Ryan ChongSoftware Team Lead

Commented:
again, check the table's name make sure it's not misspelled.

example above just intend to highlight:

SELECT 1 as src
union...
SELECT 2 as src

Open in new window

Graemewebber4technologies

Author

Commented:
So src is what I want to call something??
Ryan ChongSoftware Team Lead

Commented:
So src is what I want to call something??
yes, it's just an indicator that illustrates the source of your record. You can named it whatever you want to.
Graemewebber4technologies

Author

Commented:
Ah ok, and from here how can I define if its event_ID or itin_ID ?
Ryan ChongSoftware Team Lead
Commented:
and from here how can I define if its event_ID or itin_ID ?

something like this:

Select *
FROM
(
SELECT 'event' as src, event_IATA,event_start
FROM events
WHERE event_IATA='SGGPA' AND event_start>Now()
UNION
SELECT 'itins' as src, itin_depcity, itin_depdate
FROM itins,itin_depcity
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_depcity='SGGPA' AND itin_depdate>Now()
) As a
Order By event_start
Graemewebber4technologies

Author

Commented:
sorry, yes I got that part :P

However how i can define it in a list?

eg

If event_ID Then
 event qurey
elseif itin_ID Then
 itin query
end if
Distinguished Expert 2017

Commented:
My suggestion includes a third field in the recordset named TypeCD.  The others have taken to calling the "created" field src.  What you call it is irrelevant.  The point is that each row now identifies its source.

I do not understand your question.  The resultset now looks like:

event	Norwich		03/01/2017
event	Bridgeport	03/02/2017    
itinerary	Hartford	03/01/2017
itinerary	Bridgeport	03/02/2017

Open in new window

Graemewebber4technologies

Author

Commented:
OK, so when the results are displayed, so I can run the correct query eg:
SELECT * FROM tickets,ticket_stands,ticket_type WHERE event_ID="&PREVIOUS QUERY EVENT_ID
SELECT * FROM itin_items,itin_depdates WHERE itin_ID="&PREVIOUS QUERY ITIN_ID

Open in new window


How can I define which result is which - so I can run the correct query?
Distinguished Expert 2017

Commented:
If you want to use events and itinerary separately, why would you union them in the first place?

The two queries you posted create Cartesian Products.  Modern SQL Syntax uses specific joins unless you really don't have join fields and really do want a Cartesian Product.

I still don't understand what you are trying to do.  Please try saying it in words rather than guessing at what SQL to use.
Graemewebber4technologies

Author

Commented:
What I want is...

at each location, list all events and itineraries in combined date order
Distinguished Expert 2017

Commented:
Then open the QBE and choose the union query.  Select the columns you want.  Sort and group however you want.
Graemewebber4technologies

Author

Commented:
Sorry, im not finding that any help at all, as I am learning still as we go

I have the query that Ryan gave me, from this I want to know how i can define each result either as an itineray or an event
Distinguished Expert 2017

Commented:
If you like numbers, use the query Ryan gave you.  If you prefer to see text, use the query I gave you.  They do the same thing except mine gives you a meaningful value rather than a  1 or a 2 which no one who looks at the result will be able to interpret without opening the Union query in design view to see what 1 and 2 mean.

The QBE is the Access query builder.  I can't give you precise directions since they vary slightly from version to version.   Generally, click on the Create ribbon and choose the query designer.  switch to the queries tab and choose the union query from the list.  Then choose whatever columns you need and set the sort order to be what you want.
Graemewebber4technologies

Author

Commented:
Hi Pat, I seem to not be saying anything right to get the answer I need. So lets go back a bit...

What are the results ? eg if I Response.Write(result) what is 'result' ?

Also, what is QBE ?
Distinguished Expert 2017

Commented:
As I said -
The QBE is the Access query builder.
 The actual letters stand for Query By Example
Did you create the union query that I suggested?
What rows does it return?
How does that align with what you are looking for?
Are you going to use the Union as the recordSource for a report?  The report can do sorting and grouping for you.  The only time you would do these things in a query is if you wanted to export the query to Excel.
Graemewebber4technologies

Author

Commented:
Hi Pat, I ran your example into Access QBE and got the error: 'Syntax error in JOIN operation'

SELECT "Event" As TypeCD, event_IATA, event_start
FROM events
WHERE event_IATA = 'AUGPA' 
UNION ALL
SELECT "itinerary" As TypeCD, itin_depcity, itin_depdate
FROM itins Inner Join itin_depcity ON itins.itin_ID = itin_deps.itin_ID
WHERE itin_depcity = 'AUGPA'
Order by event_start, 1

Open in new window


I am not using this Query for any reporting, its for a webpage
Distinguished Expert 2017

Commented:
Either itin_depcity is the table name or itin_deps is the table name.  Which is it?
Graemewebber4technologies

Author

Commented:
I have just noticed it myself the table should of been 'itin_deps'

The result is:
TypeCD	event_IATA	event_start
Event	AUGPA	12/03/2015
Event	AUGPA	17/03/2016
itinerary	AUGPA	20/03/2017
itinerary	AUGPA	22/03/2017
Event	AUGPA	23/03/2017
itinerary	AUGPA	23/03/2017
Event	AUGPA	15/03/2018

Open in new window


Now possible to load them up with more variables and changing some?

Can I also ask what the significance of the 1 at the end is?

I can see now how you are defining them (with TypeCD), so from here Im guessing I can use an if statement to get the data from either and itin or event??
Ryan ChongSoftware Team Lead

Commented:
I can see now how you are defining them (with TypeCD), so from here Im guessing I can use an if statement to get the data from either and itin or event??
Yes you can, if you want to differentiate them with different layouts, etc in your ASP coding. just like;

if rs("TypeCD") = "Event" then
  '...
else 'itinerary
   '....
end if

Open in new window

webber4technologies
Commented:
Hi Pat, I have replaced some variables and added some new ones and tested it in Access - which works no problems

SELECT 'brand_events' AS ID, event_ID, event_start
FROM events,codes
WHERE event_IATA = 'AUGPA' AND event_code=code_short AND event_live=TRUE AND code_live=TRUE AND event_start>Now()
UNION ALL
SELECT 'brand_products' AS ID, itins.itin_ID, itin_depdate
FROM itins INNER JOIN itin_deps ON itins.itin_ID = itin_deps.itin_ID
WHERE itin_depcity = 'AUGPA' AND itin_live=TRUE AND itin_depdate>Now()
ORDER BY event_start, 1

Open in new window

results:
ID	event_ID	event_start
brand_products	37	23/03/2017
brand_events	232	23/03/2017
brand_events	318	15/03/2018

Open in new window


Ryan, I have also changed some of yours as well to suite

SELECT * FROM (SELECT 'brand_events' AS ID,event_ID,event_start
FROM events,codes
WHERE event_code=code_short AND event_live=TRUE AND code_live=TRUE AND event_IATA='AUGPA' AND event_start>Now()
UNION
SELECT 'brand_products' AS ID,itins.itin_ID,itin_depdate
FROM itins,itin_deps
WHERE itins.itin_ID=itin_deps.itin_ID AND itin_live=TRUE AND itin_depcity='AUGPA' AND itin_depdate>Now()) AS a
ORDER BY event_start

Open in new window

results:
ID	event_ID	event_start
brand_products	37	23/03/2017
brand_events	232	23/03/2017
brand_events	318	15/03/2018

Open in new window


Both are different Queries, what difference do they make?

I am confused which would be better to use

Could you both explain?
Ryan ChongSoftware Team Lead

Commented:
that's nothing much difference between that 2 queries, just a way how you write it.

and there's a difference between Union All and Union.

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

more ref:
https://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
Graemewebber4technologies

Author

Commented:
oh ok, so UNION ALL is similar to SELECT * FROM where UNION is SELECT itin_ID,itin_depcity FROM ??
Ryan ChongSoftware Team Lead

Commented:
so UNION ALL is similar to SELECT * FROM where UNION is SELECT itin_ID,itin_depcity FROM ??
yes, similar but not the same. the result could be different.
Graemewebber4technologies

Author

Commented:
Oh ok, I know currently both these querys give the same results, how could they differ ?
Ryan ChongSoftware Team Lead

Commented:
how could they differ ?

it's explained in the comment: ID: 42057154 posted above.

another example is:

create a table Test with column: col1

Create table Test
(
   col1 text
);

insert into Test values ('test 1');
insert into Test values ('test 2');
insert into Test values ('test 3');
insert into Test values ('test 4');
insert into Test values ('test 1');

Open in new window


SnapShot.png

UNION

Select col1 from Test
Union
Select col1 from Test

Open in new window


SnapShot2.png

UNION ALL

Select col1 from Test
Union all
Select col1 from Test

Open in new window


SnapShot3.png
Graemewebber4technologies

Author

Commented:
Ah yes, sorry, may have thought there was something else

Thanks to both you, Ryan and Pat for the 2 Querys
Graemewebber4technologies

Author

Commented:
Graemewebber4technologies

Author

Commented:
2 great solutions, thanks so much for your patients!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial