SQL Join + Subquery Problem
SQL Join + Subquery Problem
(OP)
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 |
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 |
Talk To Other Members
RE: SQL Join + Subquery Problem
CODE
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;
CODE
--------- --------- ---------
3
01-MAR-09 4 3
01-APR-09 4 3
Hope this gets you closer.
RE: SQL Join + Subquery Problem
RE: SQL Join + Subquery Problem
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).
RE: SQL Join + Subquery Problem
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!