mySQL: Query two tables for multiple results from array in first table?

[Presto]

Hey guys,

I have two tables (‘events’, and ‘options’) in mysql database that I am trying to pull data from, I thought this would be easy, but I am having a heck of a time getting it to work.

I have a column in table ‘events’ called ‘options’, that holds the ID’s of options assigned to each event, I want to query the events table and get the option titles of the options by their ID’s, currently the ID’s are saved in the column like this: ‘1,2,3,4’ etc.

This is what my query looks like:

“SELECT e.* FROM events AS e JOIN LEFT options AS o ON o.id = e.options”

This sorta works, it gets the title of the first option, but if I have multiple ID’s it only gets the first one.

[__]

Can you post your table schemas, please?

SHOW CREATE TABLE `events`
.

SHOW CREATE TABLE `options`

> I have a column in table `events` called `options`, that holds the ID’s of options assigned to each event … currently the ID’s are saved in the column like this: `1,2,3,4`

That’s likely going to be a problem, especially since you’re trying to use these values directly in a query.

Any given column should store *one* value, **not** a *collection* of values. That’s like buying a huge, fancy filing cabinet, labeling drawers for each letter of the alphabet, and then dumping all of your records into the `R` drawer (“R” for “records”).

For jobs like this, you need to use an *association table*. I can show you an example of how to do this once I see how your tables are constructed.

[Presto]

Hello traq, thanks for the reply…

CREATE TABLE `qmzi_raceschedule_events` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ordering` int(11) NOT NULL,
`state` tinyint(1) NOT NULL DEFAULT ‘1’,
`checked_out` int(11) NOT NULL,
`checked_out_time` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`created_by` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`date` date NOT NULL,
`start_time` varchar(255) NOT NULL,
`description` text NOT NULL,
`options` varchar(255) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

CREATE TABLE `qmzi_raceschedule_options` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ordering` int(11) NOT NULL,
`state` tinyint(1) NOT NULL DEFAULT ‘1’,
`checked_out` int(11) NOT NULL,
`checked_out_time` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`created_by` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`eventid` text NOT NULL,
PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

[CrocoDillon]

I’m not an expert on database design but like @traq said for many-to-many relationships you’ll need a 3rd (association) table like (simplified):

CREATE TABLE event_options (
id INT,
event_id INT,
option_id INT
)

…with constraints and indices.

[Presto]

I know it can be done with only two tables, as the backend of Joomla is currently doing it this one :O)

So I think I have it almost working, this query pulls all of the option titles from the table:

“SELECT e.*, (SELECT GROUP_CONCAT(DISTINCT o.title SEPARATOR ‘,’) FROM #__raceschedule_options AS o) optiontitles FROM `#__raceschedule_events` AS e”;

But if I try and add a WHERE IN () clause it does not work, I’m guessing becuase the WHERE IN clause wants the IDs to be within single quotes? This query does not work:

“SELECT e.*, (SELECT GROUP_CONCAT(DISTINCT o.title SEPARATOR ‘,’) FROM #__raceschedule_options AS o WHERE o.id IN (e.options)) optiontitles FROM `#__raceschedule_events` AS e”;

In this query if I manualy add the IDs wrapped in single quotes it works how I need it to.

“SELECT e.*, (SELECT GROUP_CONCAT(DISTINCT o.title SEPARATOR ‘,’) FROM #__raceschedule_options AS o WHERE o.id IN (‘1′,’3’)) optiontitles FROM `#__raceschedule_events` AS e”;

So is there a mysql function that will add single quotes before and after each ID?

[Presto]

So I was able to get it to work by replacing **WHERE IN** with **FIND_IN_SET**:

“SELECT e.*, (SELECT GROUP_CONCAT(DISTINCT o.title SEPARATOR ‘,’) FROM #__raceschedule_options AS o WHERE FIND_IN_SET(o.id, e.options)) optiontitles FROM `#__raceschedule_events` AS e”

[CrocoDillon]

Seems to me like a lot extra work for saving yourself one table. Probably lower performance too (I can imagine comparing strings is slower than comparing indices anyway). But at least it’s working :)

[Author]

Posts