I am looking for the correct syntax to combine UNION ALL and JOIN in a secelct query

leifoet · Feb 12, 2019

Given this setup :

=> tableA => with 2 fields A_NR and A_Nm - the records are (for field A_NR) e.g. numbers as 550 and 560 and 570 and 580 ... / field A_Nn = name
=> tableBB and tableCC with records whose fields BB_NR and CC_NR may contain the same numbers as in tableA
(other fields of this tables are date, amountP, amountM, ... )

to make
a list of all the fields A_NR/A_Nm with the total amountM and/or the total amountP (=TrDTV or TrCTW) of all the corresponding records from tableBB and tableCC (UNION ALL) with the same A_NR
and at the bottom the sum of all the amounts (=TTrDTV or TTrCTW)

I tried to make a SELECT query for this UNION ALL and JOIN combination, but it does not work.
Database = Access
Thanks for tips and help.

Code:

sqlJ = "SELECT BB_NR,date,number,name,amountM,amountP,DT_V,CT_W, tableA.A_NR, tableA.A_Nm,"&_
"(Select Sum([DT_V]) FROM tableBB) AS TTrDTV,"&_
"(Select Sum([CT_W]) FROM tableBB) AS TTrCTW,"&_
"(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV],"&_
"(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW]"&_
"FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null "&_

"UNION ALL "&_
"SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tabelA.A_Nm "&_
"FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null "&_
"ORDER BY tableA.A_NR ASC"

dhookom · Feb 13, 2019

This question should have been posted in an Access forum since it isn't SQL Server.
Your union queries don't have the same number of columns.
Also, if this is copied from code, you are missing spaces:

Code:

"(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW][highlight #EF2929] [/highlight]"[highlight #EF2929] [/highlight]&[highlight #EF2929] [/highlight]_
"FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null "[highlight #EF2929] [/highlight]&[highlight #EF2929] [/highlight]_

I would add:

Code:

Debug.Print sqlJ

I expect there are other similar errors.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

leifoet · Feb 13, 2019

Sorry, no problem to move the question to another forum but I do not understand this very good : i'ts still a webpage on a Microsoft Server with a sql query based on an Access database ?

Not the same number of columns ? I do not understand why I get this error because I selected the same 8 columns :
=> columns in tableAA = 8 => BB_NR,date,number,name,amountM,amountP,DT_V,CT_W
=> columns in tableBB = 8 => tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W

Spaces added.

Is the other syntax of this query OK ?
Thanks for help.

fredericofonseca · Feb 13, 2019

SELECT BB_NR --1
,date --2
,number --3
,name --4
,amountM --5
,amountP --6
,DT_V --7
,CT_W --8
,tableA.A_NR --9
,tableA.A_Nm --10

-- all 4 columns below are the difference between the 2 sides of the union
,(Select Sum([DT_V]) FROM tableBB) AS TTrDTV
,(Select Sum([CT_W]) FROM tableBB) AS TTrCTW
,(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV]
,(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW]
FROM tableA
left JOIN tableBB
ON tableA.A_NR=tableBB.BB_NR
WHERE tableA.A_Nr is not null

UNION ALL
SELECT tableCC.JAccNR --- 1
,tableCC.date --- 2
,tableCC.number --- 3
,tableCC.name --- 4
,tableCC.amountM --- 5
,tableCC.amountP --- 6
,tableCC.DT_V --- 7
,tableCC.CT_W --- 8
, tableA.A_NR --- 9
, tabelA.A_Nm --- 10
FROM tableA
left JOIN tableCC
ON tableA.A_NR=tableCC.CC_NR
WHERE tableA.A_NR is not null
ORDER BY tableA.A_NR ASC

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

leifoet · Feb 13, 2019

Thanks federicofonseca for the helpfull explanation.

-- all 4 columns below are the difference between the 2 sides of the union
,(Select Sum([DT_V]) FROM tableBB) AS TTrDTV
,(Select Sum([CT_W]) FROM tableBB) AS TTrCTW
,(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV]
,(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW]

This 4 SELECT Sum(...) 'columns' are also applicable for tableCC (not only for tableBB).
But in the query structure I do not know where and how this should be (=my query syntax problem).

Thanks for help

strongm · Feb 13, 2019

>a webpage on a Microsoft Server with a sql query based on an Access database

This forum, however, is for Microsoft SQL Server, which is not quite the same as a generic Microsoft server running any old SQL ...

dhookom · Feb 13, 2019

This might work but I would add a line after this that displays the value of sqlJ. In Access this would be Debug.Print sqlJ. In classic ASP this would be Response.Write(sqlJ)

Code:

sqlJ = "SELECT BB_NR,date,number,name,amountM,amountP,DT_V,CT_W, tableA.A_NR, tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " 
sqlJ = sqlJ & "FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null " & _
  "UNION ALL " & _
  "SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tabelA.A_Nm " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " 
  "FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null " & _
  "ORDER BY tableA.A_NR ASC "

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

leifoet · Feb 13, 2019

May I assume that after "UNION ALL " & _ the table(name) must be (Select Sum([DT_V]) FROM tableCC) AS TTrDTV, " & _ (instead of … FROM tableBB) ?

If tableCC=OK => I receive the following error :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] The number of columns in the two selected tables or queries of a union query do not match.
Column too little ?

I use classic ASP => what shoud be the anwer to "Response.Write(sqlJ)" ?

Thanks for help.

dhookom · Feb 13, 2019

leifoet,
Why are you asking us about the correct tables in the FROM clause of the subqueries?

Response.Write should display the SQL statement in the web page.

Don't reply back without copying your current code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

leifoet · Feb 15, 2019

My question 'about the correct tables in the FROM clause of the subqueries' : because I thought the table names in this part of the code should be changed in tableCC (instead of tableBB)

Because I was not sure (which of these was the correct intended query) I tested both.
My big apologies if it is not an exchange.

Code:

sqlJ = "SELECT BB_NR,date,number,name,amountM,amountP,DT_V,CT_W, tableA.A_NR, tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " 
sqlJ = sqlJ & "FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null " & _
  "UNION ALL " & _
  "SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tabelA.A_Nm " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " 
  "FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null " & _
  "ORDER BY tableA.A_NR ASC "

Code:

sqlJ = "SELECT tableBB.BB_NR,tableBB.date,tableBB.number,tableBB.name,tableBB.amountM,tableBB.amountP,tabelBB.DT_V,tableBB.CT_W, tableA.A_NR,tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " 
sqlJ = sqlJ & "FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null " & _
  "UNION ALL " & _
  "SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tabelA.A_Nm " & _
  "(Select Sum([DT_V]) FROM tableCC) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableCC) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableCC WHERE [tableCC].CC_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableCC WHERE [tableCC].CC_NR=[tableA].A_NR) AS [TrCTW] " 
  "FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null " & _
  "ORDER BY tableA.A_NR ASC "

I get the following error for both code versions
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] The number of columns in the two selected tables or queries of a union query do not match.

Where does the difference in columns (fields) come from ? in tableBB and tableCC ?
Are there any other issues that I can test?

Thanks for help.

dhookom · Feb 15, 2019

You are missing a comma at the line ending:

Code:

tableA.A_NR, tabelA.A_Nm[COLOR=#EF2929][highlight #FCE94F],[/highlight][/color] " & _

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Andrzejek · Feb 15, 2019

Duane said:
Don't reply back without copying your current code

And you didn't do that.

If you would, you would see right away (missing continuation marks):

Code:

sqlJ = "SELECT BB_NR,date,number,name,amountM,amountP,DT_V,CT_W, tableA.A_NR, tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] "
sqlJ = sqlJ & "FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null " & _
  "UNION ALL " & _
  "SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tabelA.A_Nm " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] "[red]
  "FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null " & _
  "ORDER BY tableA.A_NR ASC "[/red]

And if you fix this, and then look at your statement, you would see:

Code:

SELECT 
BB_NR,
date,
number,
name,
amountM,
amountP,
DT_V,
CT_W, 
tableA.A_NR, 
tableA.A_Nm, 
(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, 
(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, 
(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], 
(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] 
FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR 
WHERE tableA.A_Nr is not null 
UNION ALL 
SELECT 
tableCC.JAccNR,
tableCC.date,
tableCC.number,
tableCC.name,
tableCC.amountM,
tableCC.amountP,
tableCC.DT_V,
tableCC.CT_W, 
tableA.A_NR, 
tabelA.A_Nm  [red]<- missing comma[/red]
(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, 
(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, 
(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tabelA].A_NR) AS [TrDTV], 
(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] 
FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR 
WHERE tableA.A_NR is not null 
ORDER BY tableA.A_NR ASC

---- Andy

There is a great need for a sarcasm font.

Andrzejek · Feb 15, 2019

leifoet said:
My question 'about the correct tables in the FROM clause of the subqueries' : because I thought the table names in this part of the code should be changed in tableCC (instead of tableBB)

I think only YOU can answer this question - you (should) know your tables and the data in them.

By the way, about the subqueries:

Code:

...
(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[[red]tabelA[/red]].A_NR) AS [TrDTV]
...
[red]tabelA[/red].A_Nm,
...

That shows me that you've never actually run your queries and sub-queries.
Anything between SELECT and UNION ALL, and UNION ALL to the end.
And if you are unwilling to do that, no amount of help here is going to be of any substance... :-(

---- Andy

There is a great need for a sarcasm font.

leifoet · Feb 15, 2019

The good news is that the query works, even if it is only partial. - Already thanks to Dhookom, Fredericofonseca and Andrzejek for their valuable tips.

I want to say/repeat this. I am looking to make (from two 'JOIN' and already working queries for tables BB and CC) a single UNION ALL query that combines the successive two results (of the tables) into one balance (see my first message of 13 February). If sometimes I do not work in a straight line, then I want to apologize, this is rather a question of unknowing (and wanting to learn) than unwilling. In addition, English is not my native language. Everyone has his own way to achieve the goal and I am glad that specialists want to help me in that.

The UNION-Query (code see below) is actually working partial: it gives only the TrDTV-result of tableBB, where I actually expect the TrDTV-sum(tableBB + tableCC). It seems that the corresponding tableCC.JAccNR is not found.

In the codes below I changed the red tableBB to tableCC and the blue BB_NR to CC_NR, but the problem remained the same.

To solve this problem, in the query TrDTV should always be the sum of tableBB + tableCC and TrCTW the sum of corresponding values from tableBB + tableCC

But how can this ?

Code:

sqlJ = "SELECT BB_NR,date,number,name,amountM,amountP,DT_V,CT_W, tableA.A_NR, tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] "
sqlJ = sqlJ & "FROM tableA left JOIN tableBB ON tableA.A_NR=tableBB.BB_NR WHERE tableA.A_Nr is not null " & _
  "UNION ALL " & _
  "SELECT tableCC.JAccNR,tableCC.date,tableCC.number,tableCC.name,tableCC.amountM,tableCC.amountP,tableCC.DT_V,tableCC.CT_W, tableA.A_NR, tableA.A_Nm, " & _
  "(Select Sum([DT_V]) FROM [COLOR=#EF2929]tableBB[/color]) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM [COLOR=#EF2929]tableBB[/color]) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM [COLOR=#EF2929]tableBB[/color] WHERE [[COLOR=#EF2929]tableBB[/color]].[COLOR=#204A87]BB_NR[/color]=[tableA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM [COLOR=#EF2929]tableBB[/color] WHERE [[COLOR=#EF2929]tableBB[/color]].[COLOR=#204A87]BB_NR[/color]=[tableA].A_NR) AS [TrCTW] " & _
  "FROM tableA left JOIN tableCC ON tableA.A_NR=tableCC.CC_NR WHERE tableA.A_NR is not null " & _
  "ORDER BY tableA.A_NR ASC "

Andrzejek · Feb 18, 2019

leifoet said:
I actually expect the [highlight #FCE94F]TrDTV[/highlight][highlight #FCE94F][/highlight]-sum(tableBB + tableCC). It seems that the corresponding tableCC.JAccNR is not found.

So I assume you are talking about this portion of your SQL:[tt]
"([blue]Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR[/blue]) AS [[highlight #FCE94F]TrDTV[/highlight]], " & _
[/tt]
If so, you are right, "[tt]tableCC.JAccNR[/tt] is not found" because it is never mentioned in this part.

---- Andy

There is a great need for a sarcasm font.

leifoet · Feb 19, 2019

Not just that line, but all these code lines (as suggested by dhookom on 13 Feb 19 14:59)

Code:

"(Select Sum([DT_V]) FROM tableBB) AS TTrDTV, " & _
  "(Select Sum([CT_W]) FROM tableBB) AS TTrCTW, " & _
  "(Select Sum([DT_V]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrDTV], " & _
  "(Select Sum([CT_W]) FROM tableBB WHERE [tableBB].BB_NR=[tableA].A_NR) AS [TrCTW] " & _

As I wrote in my last post, the change below is not a solution :

Code:

In the codes below I changed the red tableBB to tableCC and the blue BB_NR to CC_NR, but the problem remained the same.

How can this be solved so that the result is the sum of (tableBB + tableCC)?
Thanks for tips.

Andrzejek · Feb 19, 2019

Concentrate on one problem at the time.

[tt]Select Sum([DT_V]) FROM tableBB[/tt]
Does it give you the correct value?

---- Andy

There is a great need for a sarcasm font.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I am looking for the correct syntax to combine UNION ALL and JOIN in a secelct query

leifoet

Technical User

dhookom

Programmer

leifoet

Technical User

fredericofonseca

IS-IT--Management

leifoet

Technical User

strongm

MIS

dhookom

Programmer

leifoet

Technical User

dhookom

Programmer

leifoet

Technical User

dhookom

Programmer

Andrzejek

Programmer

Andrzejek

Programmer

leifoet

Technical User

Andrzejek

Programmer

leifoet

Technical User

Andrzejek

Programmer

Similar threads

Part and Inventory Search

Sponsor