MySQL Forums
Forum List  »  InnoDB

Many to many query
Posted by: Bill Butler
Date: May 16, 2008 01:39AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Many to many query
3063
May 16, 2008 01:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.