Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help combine two tables

Status
Not open for further replies.

alohaaaron

Programmer
Mar 27, 2008
80
US
Hi, I have this query. That does a union two combine two tables. But instead of putting the 'closedloans' results next to the 'Total Units Dropped' and 'drop_date' it puts them in the rows below it. How to get them to line up on the same rows? Thanks!

SELECT COUNT(DROP_DATE) AS 'Total Units Dropped',DROP_DATE, NULL AS 'ClosedLoans' FROM inboundtracking d1 RIGHT JOIN marketing_data_nodupe d2 ON d1.DATE BETWEEN d2.DROP_DATE AND (DROP_DATE + INTERVAL 3 MONTH) AND d1.PreApprovalCode = d2.UNIQUE_CUST_ID WHERE DROP_DATE BETWEEN '2010-08-25' AND '2010-09-21' GROUP BY DROP_DATE
UNION
SELECT NULL AS 'Total Units Dropped',NULL AS 'DROP_DATE',COUNT(drop_date) ClosedLoans FROM (
SELECT MAX(drop_date) drop_date FROM marketing_data_nodupe mkt JOIN closedloanlog cll ON mkt.unique_cust_id = cll.unique_cust_id WHERE drop_date BETWEEN '2010-08-25' AND '2010-09-21' AND escrow_funding_date >= drop_date GROUP BY cll.unique_cust_id ORDER BY escrow_funding_date ASC) AS t3 GROUP BY drop_date

Code:
"Total Units Dropped"	"DROP_DATE"	"ClosedLoans"
"15001"	                "2010-08-25"	\N
"13995"	                "2010-09-20"	\N
\N	                   \N	           "5"
\N	                   \N	          "16"
 
The purpose of a union is to combine matching data from 2 tables into 1 table vertically.

You are wanting to combine unrelated data from 2 tables horizontally.

The only way I can think of would be create a temp table containing all the fields neccessary then join your two tables based on a row number then update the temp table.

The data would be totaly unrelated just exist on the same line.....

or just use Excel and copy and paste the data to the same rows.

Simi

 
This almost works but depending on what I group by in t3 it throws the other values off below??

SELECT * FROM (
SELECT COUNT(DROP_DATE) AS 'Total Units Dropped' FROM inboundtracking d1 RIGHT JOIN marketing_data_nodupe d2 ON d1.DATE BETWEEN d2.DROP_DATE AND (DROP_DATE + INTERVAL 3 MONTH) AND d1.PreApprovalCode = d2.UNIQUE_CUST_ID WHERE DROP_DATE BETWEEN '2010-08-25' AND '2010-09-21' GROUP BY DROP_DATE) t1
JOIN
(SELECT COUNT(drop_date) AS closedloans,drop_date FROM (
SELECT MAX(drop_date) drop_date FROM marketing_data_nodupe mkt JOIN closedloanlog cll ON mkt.unique_cust_id = cll.unique_cust_id WHERE drop_date BETWEEN '2010-08-25' AND '2010-09-21' AND escrow_funding_date >= drop_date GROUP BY cll.unique_cust_id ORDER BY escrow_funding_date ASC) t2
GROUP BY drop_date)
t3 GROUP BY 'Total Units Dropped'

My desired result is this but I get the other variations.

Code:
"Total Units Dropped"	"closedloans"	"drop_date"
"13995"	                         "5"	"2010-08-25"
"15001"	                         "16"	"2010-09-20"

Group by Total units dropped
"Total Units Dropped"	"closedloans"	"drop_date"
"13995"	                         "5"	"2010-08-25"
"15001"	                         "5"	"2010-08-25"

Group by t3.drop_date
"Total Units Dropped"	"closedloans"	"drop_date"
"15001"	                   "5"	         "2010-08-25"
"15001"	                   "16"	         "2010-09-20"

Group by t3.closedloans
"Total Units Dropped"	"closedloans"	"drop_date"
"15001"	                  "5"	         "2010-08-25"
"15001"	                  "16"	         "2010-09-20"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top