help combine two tables

[alohaaaron]

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

"Total Units Dropped"	"DROP_DATE"	"ClosedLoans"
"15001"	                "2010-08-25"	\N
"13995"	                "2010-09-20"	\N
\N	                   \N	           "5"
\N	                   \N	          "16"

 

[simian336]

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

 

[alohaaaron]

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.

"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"