Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: Help With Sub Query w/ Multiple tables

    Im trying to make the follow syntax into a sub query. I really new with making subqueries could someone please take a look at the bottom and help me with this?

    Basically I'm trying to make a subquery from this

    COLLTRANS@.PAYMENTAMOUNT
    COLLTRANS2@.PAYMENTDATE
    COLLACCT@.RECNUM


    My select statement would look like this

    SELECT
    SUM(COLLTRANS@.PAYMENTAMOUNT),
    COLLTRANS2@.PAYMENTDATE,
    COLLACCT@.RECNUM
    FROM COLLACCT@
    INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
    INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM
    WHERE
    COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'

    Here I was trying to put the sub queries and comparing them

    (SELECT COUNT(ACCT.RECNUM) FROM COLLACCT@ AS ACCT WHERE ACCT.CLIENTNUMBER=ClientNumber AND ACCT.DATEOFREFERRAL BETWEEN Start AND End)
    (SELECT SUM(TRANS.PAYMENTAMOUNT) FROM COLLTRANS@ AS TRANS WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16')

    SELECT
    COUNT(COLLACCT@.RECNUM),
    COLLTRANS@.PAYMENTAMOUNT,
    COLLTRANS2@.PAYMENTDATE FROM COLLACCT@
    INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM
    INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM

    (SELECT COUNT(COLLACCT@.RECNUM), COLLTRANS@.PAYMENTAMOUNT, COLLTRANS2@.PAYMENTDATE FROM COLLACCT@ INNER JOIN COLLTRANS@ ON COLLTRANS@.ACCOUNTLINK = COLLACCT@.RECNUM INNER JOIN COLLTRANS2@ ON COLLTRANS2@.RECNUM = COLLTRANS@.RECNUM WHERE COLLTRANS2@.PAYMENTDATE BETWEEN '01/01/16' AND '01/31/16'))

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I really have no idea what you are trying to do. Here are a couple of examples of sub-queries. Not sure that is what you are after though.


    Code:
    select some_column
       from some_table
    where this_column = 'that'
      and this_sequence_number = (select max(this_sequence_number)
                                     from some_table
                                  where this_column = 'that')
     and my_range_column in (select ranges
                                from range_table
                             where ? = ?)
     and exists (select 1 from existence_check
                      where am_i_alive = 'y')
    
     and (select sum(this_sum)
                from debits)     <   (select sum(that_deposit)
                                         from posts)
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •