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,121 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,853 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
{count} votes

4 answers

Sort by: Most helpful
  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 114K 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 No comments

  3. Nasreen Akter 10,766 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 No comments

  4. MelissaMa-MSFT 24,176 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 No comments