Understanding Union with Join & sub queries

[1DMF]

Hi,

I had a previous thread where I just couldn't understand how to build a select using unions and joins with sub queries and hoped someone could explain how it works so pehaps I can use this method in the future.

When you have a select with a union, are the columns meant to be the same? the example on w3schools shows the column names selected from two different tables as being the same.

So is UNION the same as adding additional records to the query from a different table where the data structure must be the same.

Where as a JOIN splices two tables together as if all columns were in a single table.

on my other thread i was having problems using a WHERE clause which included columns from the first select and columns from the JOIN nested sub-query as well as couldn't find the right place to put the 'ON' keyword.

So what is the scope for table columns within nested queries UNIONs and JOINs?

Is a query processed from left to right and what makes the right hand side part of the query able to see the left hand side columns?

Is there some presedence and is it based on operator/keyword?

Help understanding this is apprecaited as I just couldn't grasp how to do what i wanted and ended up with in-efficient VBA code to achive my goal.

I'd like to do things better and more efficient if possible.

Many thanks,
1DMF.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[randy700]

Search for union query in the Access help files.
The first link returned contains:

You can merge the results of two or more queries, tables, and SELECT statements, in any combination, in a single UNION operation. The following example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL
SELECT *
FROM Customers
WHERE OrderAmount > 1000;

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields; however, the fields do not have to be of the same size or data type .

Use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement.

 Notes 

You can use a GROUP BY or HAVING clause in each query argument to group the returned data. 
You can use an ORDER BY clause at the end of the last query argument to display the returned data in a specified order.

Randy

 

[1DMF]

Thanks for the post, though it doesn't answer the precedence question.

Though it would seem from

Use aliases only in the first SELECT statement because they are ignored in any others.

that the posts to my original thread was never going to work as subsequent queries used aliases for table names.

No wonder I was going round in circles trying to get it to work.

Though this example and description has no mention of using a join withinga union which also has sub-queries.

I've googled but cannot find information specific to the type of query my question relates to.

UNION -> JOIN -> SUB-QUERY all in one select statement.

Any pointers you can give?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[Andrzejek]

"UNION -> JOIN -> SUB-QUERY" you are talking here about 3 somehow different 'animals'.

UNION is where you use 2 of more independent queries that otherwise you can not create in one statement (in most cases). Some simple rules are: the numbers of fields in your SELECT portion must be the same, the fields must be in the same order, and the type of fields must be the same.

SELECT A.Fld1, A.Fld2, A.Fld3
FROM MyTable A
UNION
SELECT B.ABC10, B.XYZ11, B.KKK12
FROM MyOtherTable B

You have 3 columns in first SELECT and 3 columns in the second. Fld1 corresponds to ABC10, Fld2 corresponds to XYZ11, etc. If Fld1 is a Date, ABC10 must be a Date (or something that you can convert into a date in your sql), if Fld2 is a number, XYZ11 must be a number.

JOIN – that’s how you “splice two tables together” which would be a part of any SELECT sql with more than 1 table, (it does not have to be a UNION.)

SUB-QUERY is when you use SELECT inside another SELECT, something like:

SELECT fldA, FieldB, [blue]
(SELECT FldXYZ From TabelX Where ABC = 123)[/blue]
FROM MyTable
WHERE KKK = ABC’

Have fun.

---- Andy

 

[randy700]

If Fld1 is a Date, ABC10 must be a Date (or something that you can convert into a date in your sql), if Fld2 is a number, XYZ11 must be a number

This is incorrect.
The only requirement is the same number of fields.
The union query ignores data types.


Randy

 

[Andrzejek]

Randy, you may be right, but I work with ORACLE and when I try to UNION Date in first select and the Character field in the second, I get an error:

"Expression must have same datatype as corresponding expression"

But Access may be not so picky and be fine with the differences of data types....

Have fun.

---- Andy

 

[PHV]

The only requirement is the same number of fields
False, the requirement is the same number of fields of compatible type.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I'm not sure what compatible type suggests. The following uses three different type fields (numeric, date, and text) from the same table but in different column order.

Access doesn't generate an error. I suppose if any of the fields were OLE Object, this might fail.

SELECT OrderID, OrderDate, ShipName
FROM Orders
WHERE ShipName<"b"
UNION
SELECT OrderDate, OrderID,  ShipName
FROM Orders
WHERE ShipName<"b"
UNION
SELECT ShipName, OrderDate, OrderID
FROM Orders
WHERE ShipName<"b";

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

well i found this lnk regarding compund select statements with sub queries...

http://kerneltrap.org/node/16977

Interestingly the last section implies on occassion it might be better to iterate over a recordset rather than trying to comeup with a complex query.

Though it doesn't mention anything about performing additonal lookup or count queries within the loop.

And I can't see an iteration statement, so is this iteration performed by SQL and is it relative to precedence of what statement gets executed first?

With compound sub queries is the left query executed, then the results are iterated over and the next query executed for each record and so on?





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts