Thread: Query help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10

    Query help


    Here is the query I've written:

    Code:
    SELECT valid.SURNAME, valid.FST_NAME, valid.MEM_NO, temp.TRANSDATE, temp.TRANSPOST, temp.TRANSTOTAL, temp.TRANSREF, temp.POSTEDBY, temp.TRANSCODE FROM valid JOIN (SELECT MMNMBR, TRANSDATE, TRANSPOST, TRANSREF, POSTEDBY, TRANSTOTAL, TRANSCODE from valhist where TRANSTYPE = '2' and TRANSPOST >= '2013-05-06' and TRANSPOST <= '2013-05-06' UNION SELECT MMNMBR, TRANSDATE, TRANSPOST, TRANSREF, POSTEDBY, TRANSTOTAL, TRANSCODE from valtrans where TRANSTYPE = '2' and TRANSPOST >= '2013-05-06' and TRANSPOST <= '2013-05-06') temp ON valid.MEM_NO = temp.MMNMBR
    The tables valhist/valtrans store transactions and have the same structure. the valid table just stores customer info. The issue i'm having is that if there are two or more transactions with the same amount for the same customer i'm only pulling one of them and need to list each one. Thanks for any help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT valid.SURNAME
         , valid.FST_NAME
         , valid.MEM_NO
         , temp.TRANSDATE
         , temp.TRANSPOST
         , temp.TRANSTOTAL
         , temp.TRANSREF
         , temp.POSTEDBY
         , temp.TRANSCODE 
      FROM valid 
    INNER
      JOIN ( SELECT MMNMBR
                  , TRANSDATE
                  , TRANSPOST
                  , TRANSREF
                  , POSTEDBY
                  , TRANSTOTAL
                  , TRANSCODE 
               from valhist 
              where TRANSTYPE = '2' 
                and TRANSPOST >= '2013-05-06' 
                and TRANSPOST <= '2013-05-06' 
             UNION 
             SELECT MMNMBR
                  , TRANSDATE
                  , TRANSPOST
                  , TRANSREF
                  , POSTEDBY
                  , TRANSTOTAL
                  , TRANSCODE 
               from valtrans 
              where TRANSTYPE = '2' 
                and TRANSPOST >= '2013-05-06' 
                and TRANSPOST <= '2013-05-06' ) temp 
        ON valid.MEM_NO = temp.MMNMBR
    FTFY

    as for your problem, which is now tremendously easier to see, try adding the word ALL after the word UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10
    thanks

IMN logo majestic logo threadwatch logo seochat tools logo