I'm struggling to get a final result set for a 3 table hieracrchical set of data.
Hopefully, the diagrams will indicate what I have and what I'm trying to do. Briefly,
my final result set (below) should easily allow me to define a dynamic number of
checkboxes in my web site, while also allowing me to define whether the boxes are
checked, all from within a single result set. I believe that since the data is
normalized, I should be able to get a single result set, but I can't get my head
wrapped around this on... Can anyone help??
TABLE A TABLE B TABLE C
MEMBER CONTACT ALERT
(pk)$member_id -> (pk)$contact_id -> (pk) $alert_id
(fk)$member_id (fk) $contact_id
$alert_type -> values are 'local', 'state', 'nation'
Example of my filter criteria is member_id = 1 AND alert_type = 'local'
* = results of filter member_id =1
TABLE MEMBERS A
|member_id | Name |
| 1 | Alan | *
| 2 | Brad |
| 3 | Doug |
| 4 | Flo |
With this result set, I should be easily able to FOREACH my way through all 6 records
and create a checkbox for each record, and flag those records with 'local' as checked.
Can anyone help with setting up this complex query? Thanks in advance...
FROM members AS m
JOIN contacts AS c
ON c.member_id = m.member_id
JOIN alerts AS a
ON a.contact_id = c.contact_id
AND a.alert_type = 'local'
Thanks, but that was not the solution. It yielded all the records in the whole member table, including those which were not member_id =1. Plus all the alert_types showed up as NULL.
select `c`.`contact_id`, `member_id`, `email`, `phone`, `name`, `alert_type` from contacts `c`
left join contacts_selected `s` on `c`.`contact_id` = `s`.`contact_id`
group by `c`.`contact_id`
It was the 'group-by' that I needed... duh! I was oh so close.