×
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!
  • Students Click Here

*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

Jobs

SQL Join + Subquery Problem

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 |

 

RE: SQL Join + Subquery Problem

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.

RE: SQL Join + Subquery Problem

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

RE: SQL Join + Subquery Problem

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).

RE: SQL Join + Subquery Problem

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

 

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