Grow your CSS skills. Land your dream job.

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

  • # March 18, 2013 at 2:55 am

    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.

    # March 18, 2013 at 4:21 am

    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.

    # March 18, 2013 at 4:27 am

    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

    # March 18, 2013 at 6:42 am

    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.

    # March 19, 2013 at 2:11 am

    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?

    # March 19, 2013 at 3:07 am

    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”

    # March 19, 2013 at 7:44 am

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".