I could UNION the member SignIn record with their guest(s) records
OR
I could use a LISTAGG approach where the member SignIn record would also contain one field that list agg’d their guest(s).
Visually I think the LISTAGG approach would be more appealing to the end users.
User Scenario:
My plan is to hijack the SignIn Form
MemberSignInBrowse - User signs in by Inserting a SignIn record.
MemberSignInBrowse - User adds guests by clicking “+ Guests” Button.
MemberSignInBrowse - “+ Guest” Button calls SignInForm with “Change”.
SignInForm - Before window opens call GuestListSelectBrowse with “Select”
GuestListSelectBrowse - User selects 1 or more guests and clicks on “Select” button
SignInForm - A list of guest IDs is returned from GuestListSelectBrowse.
SignInForm - Call routine to populate the Guest Names field.
SignInForm - Open window and user sees Guest Names populated.
SignInForm - From this point, the form behaves as normal for “Change”.
So I need to figure out how to make GuestListSelectBrowse support multi select.
And how to return the selected values to process in a routine.
Basically because the OP wants to treat the sign-in guest the same as the tag-alongs. Me, I suspect a listagg of all of the guests is probably totally useless, whereas one guest per row (including the sign-in) might actually be useful.
So,certainly you could do:
select s.name , listagg(g.guest_name,',') within group (order by guest_name) guest_names
from signin s
left join guests g on g.sid = s.id
but your result set then has two fields: one with the signin guest name and one with a list of all of the guests they brought with them. The UNION ALL is just there to treat the signin person’s name the same as the guest names so that they all get added in the single list. Personally I suspect the listagg idea will go into the dustbin. But if you wanted a list to keep track of who got a towel or a squash racket or whatever, the UNIONed list is the probably the way to go.
Personally I would do it using an edit-in-place droplist.
On your form you have a browse that will collect all of the guest the person is bringing with them: it has the signin-id and the name of the guest. The browse displays only the guest names. You set up edit-in-place and set the name to be populated from a list of all the guests the person can choose from. That list is a queue that you populated when you entered the form.
I use that approach, for example, for collecting multiple reasons that people have for something, where the list of possible reasons has been set up in advance. So long as your list of possible guests is not in the tens of thousands, because you are loading the entire list, should work fine. Much easier than a tagging approach.