CODEWITHSUNDEEP
sqlsql-serverunion-all
J.J.
J.J.

Reputation: 1118

How to get result set from subset of UNION ALL query results

I have a query like so:

SELECT Temp.description FROM (
    (
        SELECT Clients.aliasname AS description 
        FROM ClientBilling 
        INNER JOIN [B2BSetups].[dbo].Clients ON Clients.cno = ClientBilling.cno 
        WHERE programmerCreditDate >= '11/1/2016' 
            AND programmerCreditDate < '11/29/2016' 
            AND (ClientBilling.programmer='e21' or Clients.admin='e21' or Clients.setup='e21')
    )
    UNION ALL 
    (
        SELECT Clients.aliasname + ' - ' + TradingPartners.aliasname as description 
        FROM RelationshipBilling 
        INNER JOIN [B2BSetups].[dbo].TPRelationships ON TPRelationships.relno = RelationshipBilling.relno 
        INNER JOIN [B2BSetups].[dbo].Clients ON Clients.cno = TPRelationships.cno 
        INNER JOIN [B2BSetups].[dbo].TradingPartners ON TradingPartners.tpno = TPRelationships.tpno 
        WHERE programmerCreditDate >= '11/1/2016' 
            AND programmerCreditDate < '11/29/2016' 
            AND (RelationshipBilling.programmer='e21' or Clients.admin='e21' or Clients.setup='e21')
    )           
    UNION ALL 
    (
        SELECT Clients.aliasname + ' - ' + TradingPartners.aliasname + ' - ' + RelDocs.document as description 
        FROM DocumentBilling 
        INNER JOIN [B2BSetups].[dbo].RelDocs ON RelDocs.recid = DocumentBilling.docno 
        INNER JOIN [B2BSetups].[dbo].TPRelationships ON TPRelationships.relno = RelDocs.relno 
        INNER JOIN [B2BSetups].[dbo].Clients ON Clients.cno = TPRelationships.cno 
        INNER JOIN [B2BSetups].[dbo].TradingPartners ON TradingPartners.tpno = TPRelationships.tpno 
        WHERE programmerCreditDate >= '11/1/2016' AND programmerCreditDate < '11/29/2016' 
            AND (DocumentBilling.programmer='e21' or Clients.admin='e21' or Clients.setup='e21')
    )
) AS Temp 
ORDER BY description;

I am trying to retrieve the RelationshipBilling.billedAmount, however, this is only available for the second/third select query as the top relationships. relno is required for the inner join to the necessary table.

Is there a way to retrieve these results regardless of whether they are null or not (since some will be)?

Upvotes: 0

Views: 202

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

Is this you need?

   SELECT Clients.aliasname +Temp.description  AS description,billedAmount FROM 
    (


        SELECT RelationshipBilling.programmer,TPRelationships.cno , ' - ' + TradingPartners.aliasname as description ,RelationshipBilling.billedAmount
        FROM RelationshipBilling 
        INNER JOIN [B2BSetups].[dbo].TPRelationships ON TPRelationships.relno = RelationshipBilling.relno 
        INNER JOIN [B2BSetups].[dbo].TradingPartners ON TradingPartners.tpno = TPRelationships.tpno 
        WHERE programmerCreditDate >= '11/1/2016' AND programmerCreditDate < '11/29/2016' 

        UNION ALL 
        SELECT ClientBilling.programmer, ClientBilling.cno,'' AS description,CONVERTNULL AS billedAmount FROM ClientBilling 
        WHERE programmerCreditDate >= '11/1/2016' AND programmerCreditDate < '11/29/2016' 
        UNION ALL 
        SELECT DocumentBilling.programmer,TPRelationships.cno, ' - ' + TradingPartners.aliasname + ' - ' + RelDocs.document as description ,NULL AS billedAmount
        FROM DocumentBilling 
        INNER JOIN [B2BSetups].[dbo].RelDocs ON RelDocs.recid = DocumentBilling.docno 
        INNER JOIN [B2BSetups].[dbo].TPRelationships ON TPRelationships.relno = RelDocs.relno 
        INNER JOIN [B2BSetups].[dbo].TradingPartners ON TradingPartners.tpno = TPRelationships.tpno 
        WHERE programmerCreditDate >= '11/1/2016' AND programmerCreditDate < '11/29/2016' 


    ) AS Temp 
    INNER JOIN [B2BSetups].[dbo].Clients ON Clients.cno = Temp.cno 
    WHERE 'e21' IN (Clients.setup,Clients.admin,temp.programmer)
    ORDER BY description;

Upvotes: 1

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

you can put 0 as billedAmount in first query on same order of second/third query.

Upvotes: 0

John Kuhns
John Kuhns

Reputation: 506

Just add a dummy field to the first query in the union, same data type as the real field.cast(null as decimal(9,2)) as billedAmount Then add the billedAmount to the other two queries.

Upvotes: 1

Related Questions