UNION Problem

[Soapp]

I have tow Tables: GUESTS and INVITERS

For output I use UNION.
The Problem is that only rows will be deleted where ALL values are equal.
BUT I want that SQL delete rows where GASTVORNAME AND GASTNACHNAME
is equal, because a guest can also be an inviter, and only GASTVORNAME and GASTNACHNAME in the rows is equal.


I hope you understand what I mean :)


SELECT HURZ, GASTVORNAME, GASTNACHNAME, VORNAME, NACHNAME FROM GUESTS
UNION
SELECT HURZ, GASTVORNAME, GASTNACHNAME, VORNAME, NACHNAME FROM INVITER

thanks for the help

[Vaderman]

Instead of a UNION, you would perform a LEFT JOIN as in :


SELECT HURZ, GASTVORNAME, GASTNACHNAME, VORNAME, NACHNAME FROM GUESTS <LEFT> JOIN INVITER ON GUESTS.GASTVORNAME = INVITER.GASTVORNAME


Where <LEFT> is optional and is essentially used for clarity.

By the way, for clarity only the reserved words should be in upper case. So your statement would look like the following :


SELECT hurz, gastvorname, gastnachname, vorname, nachname,
FROM guests JOIN Inviter ON Guests.gastvorname=Inviter.gastvorname


See how cleaner that looks?

Regards

John

[exterminator]

Instead of a UNION, you would perform a LEFT JOIN as in :I don't think he needs a left outer join. An equi-join would do the job for him because he says:...and only GASTVORNAME and GASTNACHNAME in the rows is equal...You know, in fact I am not sure what he actually wants. The OP should clarify his query because once he says:The Problem is that only rows will be deleted where ALL values are equal.and in his query he is using a SELECT and UNION. Regards.

[Vaderman]

I don't think he needs a left outer join. An equi-join would do the job for him

:eek: DOH!! you're so right... I did intend to say INNER JOIN!! (or Equi-Join.) I don't know whatever possessed me to write it as a Left Join statement!!

Regards

John

[exterminator]

:eek: DOH!! you're so right... I did intend to say INNER JOIN!! (or Equi-Join.) I don't know whatever possessed me to write it as a Left Join statement!!Aah..that's okay, John. You know, actually, the query isn't explanatory in itself. The OP should clarify it and then only can we put in further inputs. Regards. :thumb: