asked on

SQL Syntax Query

Heyas,

I have the following query.

SELECT Files.*
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

I would like to peform a count on this statement this what I have but it doesn't seem to be working.

SELECT 'Counting = ' + cast(COUNT(files.*) as varchar(10))
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

What I am doing wrong here.

Thank you.

ianmills2002

Hi,

You have no join on the 2 tables. Without a join clause the DB will produce a cartesian product of the 2 tables. That is, If id1 has 10 rows and id2 has 20 rows, the count os rows will be 10 * 20 = 200.

VipulKadia

Write as follows :
SELECT COUNT(Files.*) as 'Counting'
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

sachinpatil10d

Try this

SELECT 'Counting = ' + cast(COUNT(*) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
     ) tmp

Open in new window

sachinpatil10d

If you need a join as per ianmills2002 suggestions

SELECT 'Counting = ' + cast(COUNT(*) as varchar(10))
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.id = id2.id and id1.[Classification] in ('Debt', 'Asset', 'Bond')
) tmp

Umar Topia

SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) tmp

Umar Topia

If you want both the things (Table and Count)

SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

select @@RowCount

Zack

ASKER

Heya guys I keep getting syntax error below is what have tried so far.

1st Try:

SELECT 'Counting = ' + cast(COUNT(1) as varchar(10)
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try:

SELECT Files.*
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(Files.*) as 'Counting'
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

Keep getting syntax errors any ideas?

sachinpatil10d

few changes made

SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try: 

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

Open in new window

sachinpatil10d

1st Try:
SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try:

SELECT Files.*
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(*) as 'Counting'
FROM (
SELECT 'file1' as title, id2.*
FROM [file1] id1, [file1] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
FROM [file2] id1, [file2] id2
WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

there is nothing named Files

Zack

ASKER

What do you mean 'there is nothing named Files' where should it be going. I tried

SELECT COUNT(Files.*) as 'Counting'

but that didn't work either.

ASKER CERTIFIED SOLUTION

sachinpatil10d

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack

ASKER

You were correct it was error in my vb.net script I was using to pass the sql query.