×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

help combine two tables

help combine two tables

help combine two tables

(OP)
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"

RE: help combine two tables

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

 

RE: help combine two tables

(OP)
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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close