Forums

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.

traq

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 :)

treehouse : what would you like to learn today?

Forums

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