Subqueries 1

[webmigit]

I'm more familiar with Microsoft flavors of SQL so I'm having some trouble with this.

Maybe some folks here can help.. I'm trying to query a few identical-structure tables.. so I'm tossing in some server side programming to generate it fast.. but I'll just give you guys the output and its giving me an error

Attempted Method 1:

select
      (select count(*) as p1_count from d1.Products as P, pdb.Manufacturers as HM where P.ManufacturerID = HM.AutoID) as p1,
      (select count(*) as p2_count from d2.Products as P, pdb.Manufacturers as HM where P.ManufacturerID = HM.AutoID) as p2,
      (select count(*) as p3_count from d3.Products as P, pdb.Manufacturers as HM where P.ManufacturerID = HM.AutoID) as p3

Attempted Method 2: I spent more time on this one as I thought it was more likely to work than #1, because I've never tried a select query without a from clause in the master query.

select * from
      (select count(*) as p1_count from d1.Products as P, pdb.Manufacturers as HM
        where P.ManufacturerID = HM.AutoID) as p1_products,
      (select count(*) as p2_count from d2.Products as P, pdb.Manufacturers as HM
        where P.ManufacturerID = HM.AutoID) as p2_products,
      (select count(*) as p3_count from d3.Products as P, pdb.Manufacturers as HM
        where P.ManufacturerID = HM.AutoID) as p3_products

In both examples, pdb.manufacturers (alias: hm) is a table in a seperate datasource. Each table is in its own datasource with an identical setup of other tables. I'm supposed to pull all of this information together and I'm missing it somewhere.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[TonyGroves]

What you want is UNION ALL:
[tt]
SELECT COUNT(*) ...
UNION ALL
SELECT COUNT(*) ...
UNION ALL
...
[/tt]
This concatenates the three result sets. The structure of the final result set is the same as that of the first select.

 

[webmigit]

Will that return the totals seperately? Is there a way.. I need p1_products, p2_products, p3_products.

I'm of course aware that I could just query seperately, but am afraid that this would be slower, and as this model grows, which it will, it would become even slower.

I didn't design this structure, and though its cleaner, this company loves reports and this design seems to be harder to generate reports for.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[TonyGroves]

It will return three records.

 

[webmigit]

Thanks for the help.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.