Results 1 to 3 of 3

Thread: Help with UNION

  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: Help with UNION

    Hey I'm using VB to connect to a db create in SQL Anywhere v8 via ODBC ...

    Having trouble with a UNION in the following query:

    Code:
    SELECT mrentala.mmbnumbm, mmembera.mmbnamem
    FROM mrentala, mmembera
    WHERE mrentala.mmbnumbm = mmembera.mmbnumbm AND 
        (NOT (datediff(day, mrentala.daterentedm, today()) < 365)) AND 
        (mmembera.osfinesm = 0) AND 
        (mrentala.returndatem IS NOT NULL)
    UNION ALL
    SELECT mmembera.mmbnumbm, mmembera.mmbnamem
    FROM mmembera
    WHERE NOT (datediff(day, mmembera.joindatem, today()) < 365)
        AND NOT
            (SELECT mrentala.mmbnumbm
          FROM mrentala)
    My SQL is a little rusty... so any hints as to what I'm missing would be hugely appreciated.

    Thanks so much -Mark

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any hints as to what you're missing? i can't even figure out what you've got!

    seriously, if i understand what you're doing, it looks like you want all mmembera rows whether there are any related mrentala rows or not

    that's an outer join, and you can do that in one query instead of two unioned

    but there's a bit of confusion about the dates -- in the first of the queries in the union, you seem to want members with rentals but daterentedm greater than 365, but in the second of the queries, you want members without rentals but joindatem greater than 365

    if that's true, then an outer join will not do it, since they are different members

    in any case, the problem with your current union query is a syntax error in the following --

    AND NOT
    (SELECT mrentala.mmbnumbm
    FROM mrentala)

    if you are trying to determine members without rentals, this has to be a correlated subquery --

    AND NOT EXISTS
    (SELECT mmbnumbm
    FROM mrentala
    where mmbnumbm = mmembera.mmbnumbm )

    rudy
    http://r937.com/

  3. #3
    Join Date
    Mar 2003
    Posts
    2
    lol sorry I should have expained context a bit before asking ...

    From member table (mmbnumbm*, mmbnamem, joindatem...)
    and rental table (mmbnumbm*(FK), rentaldatem*, tpnumbm*(FK),...)
    I'm querying for a report that will list mmbnumbm, mmbnamem for 'dormant accounts'. 'dormant' meaning no activity within the past year --refering to those mmbnumbms in the rental table where "today - rental.rentaldatem is > 1 year", and no outstanding rentals.
    This coresponded to the first part of the query (above).

    However, I also must include all members whose mmbnumbm is not in the rental table (because they never had any activity (rentals) since joining), and whose joindatem is more than a year ago. This is meant to corespond to the second part of the query (above).

    BUT, I did eventually get it to give me the results I needed (altough I'm quite positive it is not the best/most efficient way). For the sake of it, this is what I came up with:

    Code:
    SELECT mmembera.mmbnumbm, mmembera.mmbnamem, mmembera.mmbphonem
    FROM mmembera
    WHERE (datediff(day, mmembera.joindatem, today()) > 365) AND 
        mmembera.mmbnumbm NOT IN
            (SELECT mrentala.mmbnumbm
          FROM mrentala
          WHERE mmbnumbm = mmembera.mmbnumbm)
    UNION
    SELECT mrentala.mmbnumbm, mmembera.mmbnamem, mmembera.mmbphonem
    FROM mrentala, mmembera
    WHERE mrentala.mmbnumbm = mmembera.mmbnumbm AND 
        (datediff(day, mrentala.daterentedm, today()) > 365)  AND
        (mrentala.returndatem IS  NOT NULL)
    Anyways, thanks a lot Rudy for your input though --MUCH APPRECIATED!

    Mark

Posting Permissions

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