• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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