CODEWITHSUNDEEP
ms-accessjoincountsyntax-errorunion
Ludovic Migneault
Ludovic Migneault

Reputation: 140

Syntax error on Join but no Join in query

I'm doing a request to get the number of rows in 3 different tables.

So I use an Union between 3 simples request to Count rows in each tables.

But I get an error : "Syntax error in Join Operation"

even tho I have no join in my query...

any one can help me with this please?

And here's the request : 

Select Sum(asd) as 'totalRows' 
FROM ((Select Count(*) as 'asd' from Machines) 
Union (Select Count(*) as 'asd' from Factures) 
Union (Select Count(*) as 'asd'  From Consommation)) 
 as 'tab1'

Upvotes: 1

Views: 574

Answers (2)

DRapp
DRapp

Reputation: 48159

Depends on what you really want... one row with multiple columns, or multiple rows with respective counts. Your original query was incorrect as a UNION statement is expected to be its OWN query to return its own result set in the same record format as the first query (ie: same number, column names and data types. To sample WITHOUT just using counts, take the following sample syntax.

select
      a.NumberField1,
      a.CharField1,
      a.DateField1
   from
      SomeTable a
   where
      a.SomeCondition = 1
UNION
      b.SomeField AS NumberField1,
      b.AnotherField AS CharField1,
      c.SomeDate AS DateField1
   from
      AnotherTable b
   where
      b.TestCondition = 6

The above will return all rows from "SomeTable" with its condition and include the rows from "AnotherTable" based on its condition. If there are any duplicates, the duplicate would be removed... unless you did a "UNION ALL". But notice that the union is a select statement all of its own.

Now, back to yours. Not sure if/why it failed due to being wrapped in (parens) or not but would be tried as

Select 
      Sum(asd) as 'totalRows' 
   FROM 
   (   Select Count(*) as 'asd' 
          from Machines
       UNION ALL
       Select Count(*) as 'asd' 
          from Factures
       UNION ALL
       Select Count(*) as 'asd'  
          From Consommation ) as 'tab1'

I would change to union all since... say your count from Machines AND count from Factures are BOTH 175... only ONE of the original entries would have been returned and you would be scratching your head about knowing the count was not right... Try again if coincidentally all 3 sources had the same count of 175... you would only get the single 175 record returned and be even further off than expected.

Upvotes: 1

Ludovic Migneault
Ludovic Migneault

Reputation: 140

I've solved this by changing the request, it's now : 

Select (Select Count() from Machines) as cntMachines, 
    (Select Count() From Factures) as cntFactures, 
    (Select Count(*) from Consommation) as cntConsommation 
From [Machines Or any other table]

It's not the answer to my question but it's a walk-around.

A real answer would still be appreciated. :)

Upvotes: 0

Related Questions