Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End mySQL: Query two tables for multiple results from array in first table? Re: mySQL: Query two tables for multiple results from array in first table?

#128719
Presto
Participant

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?