SQL Join + Subquery Problem

[alohaaaron]

Hi, I am trying to get the results of all dates from the dateout table dynamically as well as performing a join but instead of querying them and listing them in a query one at a time I'd like to do it dynamically where the distinct dates are selected and looped through. How might I do this? The query below is what I'm working on. Thanks, Aaron


select
outdate as 'campaign name',

(select count(*) from outtable ot2 where ot2.outdate =
outtable.outdate) as Total,

count(outdate) as 'match'
from
outtable right join intable on outvalue = invalue
where
outdate IN (select outdate FROM OutTable)
and indate between outdate and ADDDATE(outdate, INTERVAL 1 MONTH)

group by outdate

This result below is what I'd like if the query was dynamically selecting the dates and results.

campaign name total match
2009-01-05 5 3
2009-03-01 4 3
2009-04-01 4 3


Here is the data.

InTable
+-----------+---------+
| InDate | InValue |
+---------------------+
|2009-01-01 | 05 |
|2009-01-02 | 06 |
|2009-01-03 | 07 |
|2009-03-01 | 05 |
|2009-03-02 | 06 |
|2009-03-03 | 07 |
|2009-04-01 | 05 |
|2009-04-02 | 06 |
|2009-04-03 | 07 |
+---------------------+

OutTable
+---------------------+
| OutDate | OutValue|
+---------------------+
|2009-01-05 | 05 |
|2009-01-05 | 06 |
|2009-01-05 | 07 |
|2009-01-05 | 50 |
|2009-01-05 | 51 |
|2009-03-01 | 05 |
|2009-03-01 | 06 |
|2009-03-01 | 07 |
|2009-03-01 | 18 |
|2009-04-01 | 05 |
|2009-04-01 | 06 |
|2009-04-01 | 07 |
|2009-04-01 | 20 |

 

[carp]

It appears that ADDDATE is not an ANSI standard SQL function (I only find it listed under MySQL), so I'll provide a solution that works in Oracle (probably equally non-ANSI compliant!):

SELECT o.outdate, o.total, count(i.indate) AS match
  FROM (SELECT outdate, count(*) AS total 
          FROM outtable 
         GROUP BY outdate) o
       RIGHT OUTER JOIN intable i
          ON i.indate BETWEEN o.outdate 
                          AND o.outdate + 30
GROUP BY o.outdate, o.total;

My results are

OUTDATE       TOTAL     MATCH
--------- --------- ---------
                            3
01-MAR-09         4         3
01-APR-09         4         3

Note that 05-JAN-09 does not get picked up from outtable because the January indate values precede the outdate.
Hope this gets you closer.

 

[alohaaaron]

Thanks carp. I don't understand why the 05-Jan-09 doesn't display? Also when designing this query what is your approach? From the inside out? Do you have a step by step explanation? I need a better method. Thanks!

 

[carp]

The 1/5 isn't showing up because you're doing a right outer join. Try a left outer join and see what happens.

As a general rule, if I'm using a subquery or an inline view, I will do them first and make sure my output is what I expect. Then I go ahead and work on the outer query or the joins between the inline view and other tables. Also, if (as in your case) something I expect to show up is missing, I might try a full outer join to see if it shows up then. If it doesn't, then I have a serious problem. If it does, then I probably went with a left when it should have been a right (or vice versa).

 

[alohaaaron]

Thanks. That helps a lot. I noticed you use the subquery to get the count of the totals. Instead of putting in a subquery could you have put that in the main outer Select portion query? I'm wondering it would work either way?

Also finding the indate between the outdate works great but I also want to make sure the value is also matched.

So if outtable has a record:
outdate outvalue
2009-01-05 06

The intable would match if it was within one month of that date and the value was also 06
indate invalue
2009-01-10 06

How would I do that?
Thanks!