I would strongly recommend refactoring as the string transformation you
mention cannot be accomplished without external scripting assistance.
change your Bookings table to be (customer varchar(200), event_Id int(11))
This way, each booking becomes one row in the table. Five bookings = five
rows. This change also allows you to add additional fields (like price,
date, contact info, etc) on the Bookings table so that you can track that
information about EACH booking. I have no idea how you are tracking any of
that the way you have the data now.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Neal K" <[EMAIL PROTECTED]> wrote on 10/07/2004 12:33:40 PM:
> I have a quick SQL question that I hope someone can answer,
>
> I have two tables
> Events { e_id int(11), e_name varchar(200) };
> Bookings { customer varchar(200), event_list varchar(200) };
> Event_list is a string of comma separated values, eg "1,2,4" where
the
> number corresponds to Events.e_id
>
> Sample data: Events { {1,'one'}, {2,'two'}, {3,'three'} }
> Booking { {'john','1,3'} , {'jane','2,3'} }
>
> I would like some query that will return 2 rows
>
> John, 'one, three'
> Jane, 'two,three'
>
>
> Is this possible ?
>
> I inherited the schema and the data, so I would prefer not having to
> refactor anything, unless I have to.
>
> Thanks in Advance,
> Neal
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
