• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

How to put 2 queries in 1 view/sp?

Hi, how do I put these 2 queries in 1 view (or sp):

SELECT tblL2.TrID AS TransID
FROM t2
UNION SELECT tblL4LAT.TranspID AS TransID
FROM t4L
UNION SELECT T2WgCon.TID AS TransID
FROM t2V;

SELECT Query12.TransID, Count(Query12.TransID) AS CountOfTransID
FROM Query12
GROUP BY Query12.TransID;
0
joop123456
Asked:
joop123456
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Patrick MatthewsCommented:
What is it that you're trying to accomplish?
0
 
SQL_SERVER_DBACommented:
create procedure sp_astoredprocedure
as
SELECT tblL2.TrID AS TransID FROM t2
UNION
SELECT tblL4LAT.TranspID AS TransID FROM t4L
UNION
SELECT T2WgCon.TID AS TransID FROM t2V;

SELECT Query12.TransID, Count(Query12.TransID) AS CountOfTransID
FROM Query12
GROUP BY Query12.TransID;
go
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Aneesh RetnakaranDatabase AdministratorCommented:

CREATE VIEW  someView
AS

SELECT TransID, COUNT(TransID) as CountTransID
FROM (

SELECT tblL2.TrID AS TransID FROM t2
UNION
SELECT tblL4LAT.TranspID AS TransID FROM t4L
UNION
SELECT T2WgCon.TID AS TransID FROM t2V

) A
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just a note:

unless explicitely needed, you should use UNION ALL instead of UNION.
UNION will perform an implicit DISTINCT on all the resultset returned, which is most often unnecessary and even sometimes giving bad results...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
@angel,
I think in this case he is looking for the Count of the individual records, so UNION will be enough.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me be clear:
if the 3 queries return strictly non-distinct data, the results will be the same.

however, if some values are returned with multiple of the queries, or even by one single of the queries, the UNION will give different results.

create table t1 ( d int )
create table t2 ( d int )
insert into t1 values ( 1 )
insert into t1 values ( 2 )
insert into t2 values ( 1 )
insert into t2 values ( 1 )
insert into t2 values ( 3 )


select count(d) from (
select d from t1
union
select d from t2
) l

will return a different value than:

select count(d) from (
select d from t1
union all
select d from t2
) l

and you HAVE to be aware of that difference !!


0
 
joop123456Author Commented:
Did some investigation about UNION, UNION ALL and DISTINCT SELECT etc... (some of them are opposite and some are functioning the same way...IMO)

I need only one unique "TransID" per table. Just need to know if the record exists one or more times in the table. Union query above has three tables to check. So max. count per TransID = 3. (Even if there are more of the same TransID records in one table,....)

Don't know really if you are meaning the same thing or not,.... new to SQL... But this what I need...  ;-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now