Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

UNION ALL AND GROUP BY

Posted on 2011-03-04
4
Medium Priority
?
394 Views
Last Modified: 2012-05-11
Is it possible to run in the same sql 2000 query a union all and group by statement

what I am doing is creating a view from 4 different tables which hold a siteid in them.
then i want to get a unique list of them but dont really want to do this in 2 views

create view zAllSiteid

select call_siteid as siteid from tbl_calls
union all
select  inventory_siteid as siteid from tbl_inventory
union all
select orders_siteid as siteid from tbl_Orders


then I would do a


select siteid from zAllSite
group by siteid
order by siteid

can i do this in one query?





0
Comment
Question by:Chris Michalczuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35035293
try this

Select x.siteid from(
select siteid from zAllSite
group by siteid ) x

order by x.siteid
0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 2000 total points
ID: 35035311
try the concept of derived table :-

select siteid from (select call_siteid as siteid from tbl_calls
union all
select  inventory_siteid as siteid from tbl_inventory
union all
select orders_siteid as siteid from tbl_Orders)  derived_table
group by siteid
order by siteid

0
 
LVL 9

Accepted Solution

by:
mayank_joshi earned 2000 total points
ID: 35035331
select  derived_table.siteid from (select call_siteid as siteid from tbl_calls
union all
select  inventory_siteid as siteid from tbl_inventory
union all
select orders_siteid as siteid from tbl_Orders)  derived_table
group by  derived_table.siteid
order by  derived_table.siteid

Open in new window

0
 

Author Closing Comment

by:Chris Michalczuk
ID: 35035643
thanks derived table concept is good solution for what i needed to do
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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