SQL Query Help

From: Shaun Martinec <smartinec_at_usa.net>
Date: 22 Jul 2001 17:08:23 -0700
Message-ID: <c471783e.0107221608.798ba144_at_posting.google.com>


I have 2 queries that I would like to join together based on date and a domain field. However, sometimes a date will apear in one table, but not the other. If there is a date in either result I want the row to still show up. It is better explained with the actual queries.

The first query:
SELECT trunc(date_created) day, domain, count(offer_id) requests FROM offer_tracking GROUP BY trunc(date_created), domain  

First Query Example Result:

DAY       DOMAIN                             REQUESTS

--------- -------------------------------- ----------
03-JUL-01 SHAUNM 2 03-JUL-01 TEST 4 05-JUL-01 SHAUNM 1 05-JUL-01 TEST 2 11-JUL-01 TEST 1 13-JUL-01 TEST 2 19-JUL-01 TEST 8 20-JUL-01 TEST 6

The second Query:
SELECT trunc(date_created) day, domain, count(offer_id) redeems FROM offer_redeem GROUP BY trunc(date_created), domain

Second Query Example Result:

DAY       DOMAIN                              REDEEMS

--------- -------------------------------- ----------
03-JUL-01 TEST 2 09-JUL-01 TEST 1 19-JUL-01 TEST 2 20-JUL-01 TEST 1 Desired Combined Result: DAY DOMAIN REQUESTS REDEEMS RATIO
--------- -------------------------------- ---------- ---------- -----
03-JUL-01 SHAUNM 2 0 0 03-JUL-01 TEST 4 2 0.5 05-JUL-01 SHAUNM 1 0 0 05-JUL-01 TEST 1 0 0
...and so on

Any help in writing a query that accomplishes the desired result would be great. I've looked at intersection and union, but I can't seem to get the correct result. Thanks in advance.

     -Shaun Received on Mon Jul 23 2001 - 02:08:23 CEST

Original text of this message