Many to many query
All,
Thanks in advance for any help you can offer. I'm using MySQL-4.1.22. I have two tables:
notify
--------
notify_id
notify_name
notify_address
user_id
server_notify
-------------
server_id
notify_id
Essentially, I need to generate an arbitrary number of checkboxes based upon the number of rows in notify. For example, if there are two rows of data in notify, I would generate 2 checkboxes on my form. The trick here is that when the form is generated, I also want to know whether that notification is tied back to a server or not (server_notify). I'm pretty close to a solution with the following query, but it returns 3 rows that need to be consolidated. Essentially, what I would like to do is just have 2 rows returned (based upon the number of unique values in notify_id) which is 2 in this case. I'd then like to make sure that the result incorporates numeric values instead of the null values during the consolidation process.
Query:
SELECT notify_id, null as notify_name, null as notify_address, server_id FROM server_notify where server_id = 1
union
select notify_id, notify_name, notify_address, null as server_id from notify where user_id = 1
Result:
1,NULL,NULL,1
1,"Bill Email","bill@butler.net",NULL
3,"Karen","karen@butler.net",NULL
Desired Result Instead:
1,"Bill Email","bill@butler.net",1
3,"Karen","karen@butler.net",NULL
The end result here is that I have all values from notify regardless of the content in server_notify. I'd like to avoid a LEFT JOIN scenario. I've been down that road and it seems pretty hopeless for this particular case. I really just need a clue as to how to consolidate the results of my union in a clever way so that the nulls are replaced by the available real values.