One to many query
Well my friends, I have been pulling my hair out for over a day and I finally had to admit I needed help. I haven't had much experience with one to many relationships and this particular query is driving me batty and I don't think any of my Webassist extensions can help me with this one...haha
Ok, I am generating some checkboxes via a table like so:
<?php do { ?>
<input <?php if (!(strcmp($row_venues_RS['userID'],116))) {echo "checked=\"checked\"";} ?> name="venueID_<?php echo $row_venues_RS['venueID']; ?>" id="venueID_<?php echo $row_venues_RS['venueID']; ?>" type="checkbox" value="<?php echo $row_venues_RS['venueID']; ?>" />
<?php echo $row_venues_RS['venueName']." ".$row_venues_RS['venueID']; ?><br />
<?php } while ($row_venues_RS = mysql_fetch_assoc($venues_RS)); ?>
This query almost works. The proper venue (or venues) is checked, but as you can guess I get duplicates:
SELECT venues.venueID, venues.venueName, venue_user_access.userID FROM
venues INNER JOIN venue_user_access USING (venueID) WHERE venues.venueMasterID = %s AND venueStatus = 1 ORDER BY venueName ASC
These checkboxes are on an update record page so my goal is to show all venues tied to the venueMasterID (the value is stored in a sessions variable) from the venues table and check the ones that userID 116 has access. The access table essentially has a venueID and userID field. Is this possible with a single query? I haven't written a lot of complex ones therefore I may be missing some function to make this easier.
I have read up on and tried DISTINCT and GROUP BY, but never got the results I desired. So anyway, I am posting on the chance someone has an idea.
Thanks in advance,
Twitch
