I currently have a venues table which I’d like to associated with a number of different categories (table to build) so that each venue can have a number of categories attached to it.
Just wondering if this one table will be the right approach for this (as mentioned above #1)?
And do I need the KEYS, a little over kill?
The cat_name should also be unique to the cat_id, do I need some attributes onto this column?
Or maybe I have this all wrong, I need a separate categories table?
Any ideas how to achieve this?
Or this is correct I should be using php to do this work?
SELECT v.venue_id, c.category
FROM venues v
INNER JOIN venue_categories vc
ON ( v.id = vc.venues_id )
INNER JOIN categories c
ON ( c.id = vc.categories_id )
WHERE v.active =1
ORDER BY v.venue_id ASC
Cool, I thought that might be the case, yes using PHP.
Just wondering, and for reference, is what I suggest above possible using SQL with the code we have using three tables?
Been reading lots of stuff trying to figure this out, the only thing that was coming close was GROUP BY, though couldn’t quite get this to work with the current code.
But seriously, I think you’re right, this will be much easier using PHP I think, done something similar with one of our previous threads using the events and dates as the titles. I just wanted to make sure I was not missing something, generally do the hard work with SQL, though in this instance, what I have above should suffice and probably the most we can push this query.
Ok, thanks for sharing the knowledge again Rudy, appreciate the help!
Interesting… though surely bad database design if you need to join more than ten, maybe not, for the more complex and bigger datasets.
I agree
Nice to know. Feel at ease now using multiple tables within one query - I have a bunch of smaller queries I’d like to combine into one so this would be a good time to use this approach.
Hey Barry! Here’s what you’re looking for. You need a GROUP_CONCAT and a GROUP_BY GROUP_CONCAT takes multiple strings and concatenates them together with ‘,’ as a separator.
I would prefer this operation in SQL over PHP. MySQL is very good at relationships between data, maximize it’s strengths.
I created a sqlfiddle so you can see it in action:
SELECT v.id,
GROUP_CONCAT(c.category) AS categories
FROM venues v
INNER JOIN venue_categories AS vc
ON ( v.id = vc.venues_id )
INNER JOIN categories AS c
ON ( c.id = vc.categories_id )
WHERE v.active = 1
GROUP BY v.id;