• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Two Select Queries results into a single query

I have two select queries with slightly different Where Clauses.
The difference is that the date is being filtered by dates value in Transactions table and the other query is using the date filter from Stmnledger table.
They are joined by a PK ID so there is a link between the two tables.
Here are the results of the two queries run separately.

 Results 1
Here are the two select statements

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
						 
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals




/*--------------------------------------------------------------------------------------*/
SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) AS Stmnunit, 
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
						 
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months

Open in new window


dataset.txt
0
yo_bee
Asked:
yo_bee
1 Solution
 
Brian CroweDatabase AdministratorCommented:
Just join those two queries together

SELECT A.Month, A.Professionals, A.FirstName, A.Units, A.Value,
   B.StmnUnit, B.StmnValue
FROM (<first query>) AS A
INNER JOIN (<second query>) AS B
   ON A.Professionals = B.Professionals
   AND A.Month = B.Month

Adjust the joining fields if I'm misinterpreting the column but definitely include month
0
 
sarabhaiCommented:
You need to union these two result set by using following query.

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals


UNION ALL


SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) ,
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) , MONTH(StmnLedger.StmnDate)
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
ORDER BY Months
0
 
sarabhaiCommented:
Union all ensures that all the records from both the queries should come in result set.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
What is exactly your question ?
0
 
yo_beeDirector of ITAuthor Commented:
@sarabhai  I tried the Union and it does somewhat the desired results, but rather than putting the data in the correct column name it groups all Units as one column and all Values as another.
Where is should show

5 Records with values in the Units column and 5 Records with values in the StmnUnits column.

UnionAll Img
@ BriCrowe
When joining you stated <Query 1>   How to I know the name of the query?


@Racimo

The Queries are from the same Join Tables, but the Units are summed up by a date field.
The first query uses the Date field from Transactions table while the second query uses date field from StmnLedger Table.
These two tables have a linked PK between so if I pull a record from stmnledger table I can also return values from the Transactions table

 Daigram Img
So my desired results would be to have a final output of.
Profname, Units, Value, StmnUnits, StmnValue, Months.
0
 
sarabhaiCommented:
I think you need result with their column name also to proper understanding.
so here you can check

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                        NULL as  Stmnunit,NULL as Stmnvalue, MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals

UNION ALL

SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) AS Stmnunit,
                         NULL AS Units, NULL as Value  ,SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months
0
 
yo_beeDirector of ITAuthor Commented:
Thanks for you help.
Here is my final query.
I added Nulls for the first query for  StmnUnits and StmnValue
Did the same for units and Value in query 2.


SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         null as StmnUnits,null as StmnValue,MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals


union all

/*--------------------------------------------------------------------------------------*/
SELECT        Professionals.Professionals, Professionals.FirstName,null as Units, Null as Value,SUM(Transactions.StmnUnits) AS Stmnunit,
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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