Solved

SQL cross join syntax problem

Posted on 2011-09-12
8
699 Views
Last Modified: 2012-05-12
select count(*) 'VolumeA' from tableA
cross join
select count(*) 'VolumeB' from tableB

error says "Incorrect syntax near the keyword 'select'"

I simply like the counts to be side by side.

Suggestions?
0
Comment
Question by:simplyfemales
8 Comments
 
LVL 5

Expert Comment

by:Brian Chan
ID: 36527158
instead, try this

select count(*) 'VolumeA' from tableA
cross join
(select count(*) 'VolumeB' from tableB)
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 36527172
Ooooo.... What am I doing?

It should be:

Select tabA.VolumeA, tabB.VolumeB
(select count(*) 'VolumeA' from tableA) as tabA
cross join
(select count(*) 'VolumeB' from tableB) as tabB
0
 

Author Comment

by:simplyfemales
ID: 36527220
Select tabA.VolumeA, tabB.VolumeB
(select count(*) 'VolumeA' from tableA) as tabA
cross join
(select count(*) 'VolumeB' from tableB) as tabB

doesn't work.

Incorrect syntax near the keyword 'select'
Incorrect syntax near ')'
Incorrect syntax near the keyword 'as'
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Accepted Solution

by:
Brian Chan earned 125 total points
ID: 36527239
My typo..... I missed the FROM

Select tabA.VolumeA, tabB.VolumeB
FROM
(select count(*) 'VolumeA' from tableA) as tabA
cross join
(select count(*) 'VolumeB' from tableB) as tabB
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36527242
cross join works like this

chabge ID_A and ID_B as per your column names

select count(ID_A) 'VolumeA' , Count(ID_B) 'VolumeB'  from tableA
cross join tableB
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
ID: 36527262
An you probably just want something simple like this:

SELECT (SELECT COUNT(1) FROM tableA) AS VolumeA
      , (SELECT COUNT(1) FROM tableB) AS VolumeB
;

Open in new window

0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 36527423
@simplyfemales, seriously if you are not obsessive with using cross join, mwvisa1's solution is much cleaner cut.
0
 

Author Closing Comment

by:simplyfemales
ID: 36530176
Both great suggestions.  Thanks.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 39
Help Required 2 32
get column names from table in vb.net 8 28
why sql server only update some statistics in the database ? 3 22
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question