Share via

Union Query

Brian collins 141 Reputation points
2021-02-18T16:41:00.443+00:00

Hi,

I have the following query which I would like to combine into one single query. Please assist on how to accomplish this task. 

Select Cust.ID, Cust.Name, Cust.RecordID,M.Question_Txt
from Customer Cust, Profile P, Activity M
Where Cust.ProfID = P.ProfID
and P.ActivityGUID = M.ActivityGUID
and P.Record_Guid IS NULL

UNION

Select Cust.ID, Cust.Name, Cust.RecordID,''
from Customer Cust, Profile P, Activity M
Where Cust.ProfID = P.ProfID
and P.ActivityGUID = M.ActivityGUID
AND P.Record_Guid IS NULL
AND P.ProfileGUID IS NULL

The first query returns 10 records and the second query return 2 records. A total of 12 records.

However, If I combine the two queries into a single query like below, it returns only 10 records. Where am I going wrong? 

Select Cust.ID, Cust.Name, Cust.RecordID, M.Question_Txt
from Customer Cust, Profile P, Activity M
Where Cust.ProfID = P.ProfID
and P.ActivityGUID = M.ActivityGUID
AND ((P.Record_Guid IS NULL) OR  (P.Record_Guid IS NULL AND P.ProfileGUID IS NULL))
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Cooper 8,466 Reputation points
    2021-02-18T16:58:40.533+00:00

    The second half of the UNION returns 2 rows, but those 2 rows were also returned by the first query. But your combined query will return each row only one time. You really only have 10 rows.

    Tom

  2. Viorel 114.7K Reputation points
    2021-02-18T17:14:01.48+00:00

    Check this query: 

    select Cust.ID, Cust.Name, Cust.RecordID, case when P.ProfileGUID IS NULL then '' else M.Question_Txt end as Question_Txt
from Customer Cust, Profile P, Activity M
where Cust.ProfID = P.ProfID
and P.ActivityGUID = M.ActivityGUID
and P.Record_Guid IS NULL
    0 comments
  3. Nasreen Akter 10,791 Reputation points
    2021-02-18T17:14:40.963+00:00

    Hi @Brian collins ,

    Please try the following. Thanks! 

    Select Cust.ID, Cust.Name, Cust.RecordID,   
 CASE WHEN P.Record_Guid IS NULL AND P.ProfileGUID IS NULL THEN '' ELSE M.Question_Txt END AS Question_Txt  
 from Customer Cust, Profile P, Activity M  
 Where Cust.ProfID = P.ProfID  
 and P.ActivityGUID = M.ActivityGUID  
 AND ((P.Record_Guid IS NULL) OR  (P.Record_Guid IS NULL AND P.ProfileGUID IS NULL))
    0 comments
  4. MelissaMa-MSFT 24,191 Reputation points
    2021-02-19T05:47:01.51+00:00

    Hi @Brian collins ,

    Please refer below: 

     select distinct id,name,RecordID  
 ,case when ProfileGUID is null and n=1 then '' else Question_Txt end Question_Txt  
  from (Select Cust.ID, Cust.Name, Cust.RecordID,M.Question_Txt,P.ProfileGUID  
 from Customer Cust, Profile P, Activity M  
 Where Cust.ProfID = P.ProfID  
 and P.ActivityGUID = M.ActivityGUID  
 AND P.Record_Guid IS NULL) a  
  CROSS APPLY(SELECT 1 UNION SELECT 2)N(n)

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments