JOIN Multiple Data in one Field

Databases
1

Hi. I have a table that looks like this:

Venue
(VenueID,Name,FacilityIDs) VALUES (‘1’,‘Pool Side’,‘1|2’);

And another table that looks like this:

Facilities
(FacilityID,Name) VALUES (‘1’,‘Pool’),(‘2’,‘Bar’)

Is there any way I can query my Venue table to look like this:

$row[VenueID] = ‘1’
$row[Name] = ‘Pool Side’
$row[Facilities] = ‘Pool|Bar’

Thanks! Hope to hear from you guys!!!

2

The problem is you want to join values in a CSV column. Please normalize the Venue table, getting rid of those multiple values in one column.
Create a third table, Venuefacilities, that’ll contain a row for each VenueID-FacilityID.

Then joining will be easy, and you can use GROUP_CONCAT to create the output you want (or do it in PHP or whatever language you use).

3

Oh ok. So with this type of structure, this can not be done?

Thanks! I’ll just do it on PHP I guess.

Cheers!

4

Just out of curiousity: why don’t you want to change the database structure?
Multiple values in a column is a big no-no, and is going to complicate things sooner or later (as you just found out).

5

I am a bit behind schedule, and changing the structure might delay me some more.

But after thinking about it, I am finding that what you suggested might even be faster. So I think I’m just going to change the database structure.

I was thinking updating the FacilityIDs field using Checkboxes and AJAX would be much more difficult than to have a separate table where I just insert and delete a row onclick.

Thank you so much for the help!