Help with UNION

themarkman · #1 (**permalink**) 03-23-03, 02:31

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

r937 · #2 (**permalink**) 03-23-03, 05:31

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/

themarkman · #3 (**permalink**) 03-24-03, 01:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech