Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Join + Subquery Problem

Status
Not open for further replies.
alohaaaron

alohaaaron

Programmer
Joined
Mar 27, 2008
Messages
80
Location
US
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 |

 
Sort by date Sort by votes
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!):
Code: 
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
Code: 
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
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!

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question Question
Cross Join in an Insert Into
Replies
1
Views
584
carp
carp
BobThornton
  • Locked
  • Question Question
windows command in a sql script
Replies
1
Views
605
carp
carp
jchewsmith
  • Locked
  • Question Question
Update Query with a MAX(Date) subquery
Replies
1
Views
688
JarlH
JarlH
jchewsmith
  • Locked
  • Question Question
SQL Syntax
Replies
1
Views
550
JarlH
JarlH
SashaBuilder3
  • Locked
  • Question Question
left join with "soft" condition
Replies
2
Views
370
SashaBuilder3
SashaBuilder3

Part and Inventory Search

Sponsor

Back
Top