Link to home
Create AccountLog in
Avatar of MikeMCSD
MikeMCSDFlag for United States of America

asked on

UNION of 3 tables

The tables used:

Events
EventID     EventName     Active
----------- -------------         ------
1           Birthday               0
2           Christmas             0
3           Easter                  0
4           Halloween            0
....

MemberEvent
MemID       EventID       Active
----------- -----------     ------------    
61                1                  1
61                2                  1
62                1                  1
63                3                  1
......

CustomEvents
EventID     memid       EventName       Active
----------- ----------- ---------------------- - ------
1000         61          Office Party               1
1001         61          Garage Sale              1
1002         61          Retirement                0
.............

I want the "Active" field to display True if it is True in the MemberEvent table,
Like this (@MemID = 61) :

EventID     EventName        Active
----------- ---------------------- ------
1           Birthday                  1
2           Christmas               1
3           Easter                     0
4           Halloween               0
1000        Office Party           1
1001        Garage Sale          1

but I'm getting this:

EventID     EventName        Active
----------- ---------------------- ------
1           Birthday                  0
2           Christmas               0
3           Easter                     0
........

Here is what I have:
SET @MemID = 61

SELECT Events.EventID, Events.EventName, Events.Active
FROM Events
LEFT JOIN MemberEvent ON (Events.EventID = MemberEvent.EventID AND MemID = @MemID)
WHERE MemberEvent.EventID IS NULL
UNION

SELECT Events.EventID, Events.EventName, Events.Active
FROM Events
INNER JOIN MemberEvent ON Events.EventID = MemberEvent.EventID
WHERE MemID = @MemID
UNION

SELECT CustomEvents.EventID, CustomEvents.EventName, CustomEvents.Active
FROM CustomEvents
WHERE (MemID = @MemID AND (CustomEvents.Active = 1))

* note I write to the MemberEvent table when an Event becomes "Active", and
   delete the row when the Event is no longer Active.
ASKER CERTIFIED SOLUTION
Avatar of schlepuetz
schlepuetz

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of schlepuetz
schlepuetz

You are always taking the active field from the Events table in which it has a value of 0.  The active needs to come from the MemberEvent Table.  Or you could just make Active = 1 in the middle select statement because if it is returned by that statement then it would be Active.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of MikeMCSD

ASKER

works .. thanks guys