How to append rows to result from SELECT query in SQL?

Question

I have a query which gives me desired result. Column names are like this(which I am getting from the query):

RXID |DrName |SBOID |SBOName |RxHonoredDate |RxnHonoured |CallRecievedFrom |MobileNo

Now I have one table named 'SampleRepeat'. This have following columns

DrID |  CallRecievedFrom |  Mobile | SBOID  |RxnHonoured

(Here we'll fetch DrName through DrID from table TblDr and SBOName through SBOID)

This is my query:

select G.RXID, NoOfRx, DrName,HospitalName,G.EmpCode AS SBOID ,TM_Name AS SBOName,CONVERT(DATETIME,H.CreatedDate) AS RxHonoredDate, DrSpeciality AS Speciality,
convert(DATETIME, G.CreatedDate) AS [RxGeneratedDate],COALESCE(H.rows, 0) AS RxnHonoured,
CallRecievedFrom,MobileNo ,G.HQ
from(
select RXID,SUM(RxGenerate) as NoOfRx, DrName,HospitalName,RX.EmpCode,TE.TM_Name,   DrSpeciality,convert(DATE,RX.CreatedDate)as CreatedDate,TE.Territory AS HQ
from tbl_rx RX
  left join tblEmployee TE on  TE.TM_Emp_Id=RX.EmpCode
GROUP BY RX.EmpCode,RX.DrName,RX.HospitalName,RX.CreatedDate,RX.DrSpeciality,TE.TM_Name,RX.RXID,TE.Territory 
)G
left join
( SELECT EmpCode,DrID,CreatedDate, SUM(MedToPCount) AS rows,CallRecievedFrom,MobileNo FROM tbl_MedicinToPatient WHERE Status = 'Delivered' GROUP BY EmpCode,DrID,CreatedDate,CallRecievedFrom,MobileNo
)H 
on H.DrID=G.RXID ORDER BY TM_Name, H.CreatedDate ASC

I want to append this table values to end of query result and all other columns will be null. I have tried Union all but no success. How do I do that? Any help would be much appreciated.

How are you trying to do it? Can you also add the query you are using? A simplified version that shows the problem. The union all should do it, as long as you specify nulls for the missing columns. Check this out. — Rigerta
– Rigerta, Commented Jun 13, 2018 at 13:22
UNION ALL is indeed what you need. "No success" is a poor problem description, we need to see what you tried and in what way it failed. — Jiri Tousek
– Jiri Tousek, Commented Jun 13, 2018 at 13:23
What is your expected result here exactly? And why haven't you posted the SQL that you tried? — Thom A
– Thom A ♦, Commented Jun 13, 2018 at 13:24
When I Union all it says :All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. — Dungeon
– Dungeon, Commented Jun 13, 2018 at 13:26

marc_s · Accepted Answer · 2018-06-13 20:28:40Z

5

UNION with a calculated column or UNION ALL should work.

SELECT
    *
FROM
(
    SELECT
        OrderID = 1 
        RXID, 
        DrName,
        SBOID,
        SBOName,
        RxHonoredDate,
        RxnHonoured,
        CallRecievedFrom,
        MobileNo  
    FROM 
        Query Q

    UNION

    SELECT 
        OrderID = 2
        RXID = NULL, 
        DrName = DrID, 
        SBOID = NULL, 
        SBOName = SBOID, 
        RxHonoredDate = NULL,  
        RxnHonoured, 
        CallRecievedFrom, 
        MobileNo = Mobile 
    FROM 
        SampleRequest) AS X
ORDER BY
    OrderID

edited Jun 13, 2018 at 20:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 13, 2018 at 13:26

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EzLo · Accepted Answer · 2018-06-13 13:26:29Z

1

Make sure that the order and data type of each column from both SELECT match.

SELECT
    RXID,
    DrName,
    SBOID,
    SBOName,
    RxHonoredDate,
    RxnHonoured,
    CallRecievedFrom,
    MobileNo
FROM
    YourFirstTable AS T
UNION ALL
SELECT
    RXID =  NULL,
    DrName =  NULL,
    SBOID =  T.SBOID,
    SBOName =  NULL,
    RxHonoredDate =  NULL,
    RxnHonoured =  T.RxnHonoured,
    CallRecievedFrom =  T.CallRecievedFrom,
    MobileNo =  T.Mobile
FROM
    YourSecondTable AS T

answered Jun 13, 2018 at 13:26

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to append rows to result from SELECT query in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related