Solved

Union Query

Posted on 2014-04-24
6
281 Views
Last Modified: 2014-04-25
I am very unfamiliar with union queries... I have 5 tables that I would like to show the results of on one query. They all share one common column that is "DateSpec" there are only 4 different values that that could be. The ending table would be a 4 rowed table of the 4 datespec values. Then the columns would be all the different columns of the 5 tables.

DateSpec            Column1                           Column2
Value1                 ValuefromTable1              ValuefromTable2
Value2                 ValuefromTable1              ValuefromTable2
Vlaue3                 ValuefromTable1              ValuefromTable2
Vlaue4                 ValuefromTable1              ValuefromTable2

There would be about 15 tables in the end. Any idea? I think it is simple... I just am very unfamiliar with union queries. THanks!
0
Comment
Question by:cansevin
  • 2
  • 2
  • 2
6 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40021862
Union queries can be thought of as stacking the individual query results on top of one another.  They need to have the same number of columns and each column's data types in each contributing query should to be the same.

It looks to me like you want to join these tables together by their key field and each table's column(s) would be added as needed.  If the key might not exist in a table, you would use a left join (or right join) to that table.
0
 
LVL 8

Accepted Solution

by:
Ganapathi earned 500 total points
ID: 40021913
I guess this is what you wanted.

The 5 tables you are talking about should have same set of columns. If not, not an issue. Identify the maximum number Columns from the 5 tables. Use default values(Blank or NULL) for the Tables that do not have all the columns(table having maximum number of columns)

Check out the below example. Hope it helps.

CREATE TABLE Table1(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table2(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5))
CREATE TABLE Table3(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table4(DateSpec CHAR(5),Column1 CHAR(5),Column2 CHAR(5),Column3 CHAR(5))
CREATE TABLE Table5(DateSpec CHAR(5),Column1 CHAR(5))

INSERT INTO Table1 SELECT 'Spec1','Val1','Val2','Val3'
INSERT INTO Table2 SELECT 'Spec2','Val1','Val2'
INSERT INTO Table3 SELECT 'Spec3','Val1','Val2','Val3'
INSERT INTO Table4 SELECT 'Spec4','Val1','Val2','Val3'
INSERT INTO Table5 SELECT 'Spec5','Val1'

SELECT DateSpec,Column1,Column2,Column3 from Table1 UNION
SELECT DateSpec,Column1,Column2,'' as Column3 from Table2 UNION
SELECT DateSpec,Column1,Column2,Column3 from Table3 UNION
SELECT DateSpec,Column1,Column2,Column3 from Table4 UNION
SELECT DateSpec,Column1,'' as Column2,''as Column3 from Table5

Open in new window

Result:

DateSpec Column1 Column2 Column3
-------- ------- ------- -------
Spec1    Val1    Val2    Val3    
Spec2    Val1    Val2            
Spec3    Val1    Val2    Val3    
Spec4    Val1    Val2    Val3    
Spec5    Val1

Note: If Key values are missing in any of the 5 tables, then you will have to go with joins as mentioned by Aikimark.
0
 

Author Comment

by:cansevin
ID: 40022309
Thanks guys. Keys values may possibly be missing. I am going to have to try the Join option. If you can provide any an example of a Join. I don't understand the "Left join" and "Right Join".

There my be situation where a table has all, none or some of the information in the rows.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 8

Expert Comment

by:Ganapathi
ID: 40022332
Check the Result I have given above. Table2 and Table4 do not have all the information, but still they are pulled out with blank information. You may not need Joins here as it included all the results from all the tables.

The tables many need to have the same Key_column names. But they should be of same data types in all the tables.

Refer the below link if you want to know about joins. A good illustration.

http://www.techonthenet.com/oracle/joins.php
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40022360
If you need to join two or more tables, with all rows from one table and just the matching-key rows from the second table, you need to change the join type keyword from a plain JOIN to either a LEFT JOIN or a RIGHT JOIN.

When using the query design wizard, you drag the key field(s) between table listboxes to join them.  You then need to right click on the connecting line and select Join in the pop-up menu.  This will allow you to change the join type.
0
 

Author Comment

by:cansevin
ID: 40022568
Thanks guys... I think I am almost there. I have code for my query below. The problem is, there is SOME data for the row of "LASTWEEK". That data isn't showing up. It seems that only data that is showing is where every column has a value. If only  a couple columns of a row have a value, then I want the row to still show up. Not sure if that makes sense. I have also attached a screen shot of my resulting query and the design view of it.

SELECT DaySpecs.DaySpec, Avg(qryBookingDayswithYearAllAverages.AvgOfAvgOfSumOfExtendedPrice) AS AvgOfAvgOfAvgOfSumOfExtendedPrice, Avg(qryBookingDayswithYearAllAverages.AvgOfCountOfDAY) AS AvgOfAvgOfCountOfDAY, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent
FROM ((((DaySpecs INNER JOIN qryBookingDayswithYearAllAverages ON DaySpecs.DaySpec = qryBookingDayswithYearAllAverages.DateSpec) INNER JOIN qryStatsforDailySalesReportFINAL ON DaySpecs.DaySpec = qryStatsforDailySalesReportFINAL.DateSpec) INNER JOIN qryReachOutSalesALL ON DaySpecs.DaySpec = qryReachOutSalesALL.DateSpec) INNER JOIN qryReachOutALL ON DaySpecs.DaySpec = qryReachOutALL.DateSpec) INNER JOIN qryBookingTotalsAllSums ON DaySpecs.DaySpec = qryBookingTotalsAllSums.DateSpec
GROUP BY DaySpecs.DaySpec, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent;

Open in new window

Screen-shot-0425.pdf
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now