MySQL joining two tables when ID from one table is in comma string in other table


#1

Hi,
I’m working on someone elses database so the design is not my own.
There is a table for a “style” (tblstyle) with the ID styleID - this table lists different styles.
There is a main product table (tblproducts), one of the fields in the product table contains all the various styles that are applicable to this product - these styles (ID’s) are stored in a TEXT file as a comma separated list (e.g. 1,2,3).
I need to join the tables so that when the product table is queried the list of available styles only show ones that appear in the products table - e.g.

List of styles:
1 Summer
2 Sun
3 Flowers
4 Snow
5 Landscape

Products:
Product ID 1 - styles 1,3
Product ID 2 - styles 4
Product ID 3 - styles 3,5

So if I queried the products table and my results only returned products 1 and 3 the list of styles in those products would be 1,3,5

This is my code at the moment that isn’t working:

SELECT styleId, styleText 
FROM tbstyles, tblproducts 
WHERE ***my product query here*** 
AND styleId IN (prodStyle) 
GROUP BY styleId 
ORDER BY styleText ASC

…but this is just showing a couple of styles, it’s not returning all the styles it should.

I know it’s not best practice to have a comma string of ID’s in the products table but this isn’t my table and I have to work with what is there.

Can anyone help.

Many thanks,
Scim


#2

Sounds to me like you’re trying to do a subquery first, and then do an IN.
You… may be able to do it using the MySQL regex implementation, but my impulse is to say it’s going to be easier to pull it back to the intermediate language for parsing and run two independent queries…


#3

This is ugly, and @r937 probably knows a much more elegant solution, but this works

SELECT products.id
     , style_desc
  FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 
                   UNION ALL SELECT 4 UNION ALL SELECT 5
                   UNION ALL SELECT 6 UNION ALL SELECT 7
                   UNION ALL SELECT 8 UNION ALL SELECT 9
                   UNION ALL SELECT 10) numbers 
  INNER JOIN products
     ON CHAR_LENGTH(products.product_desc)-CHAR_LENGTH(REPLACE(products.product_desc, ',', ''))>=numbers.n-1
  INNER JOIN styles 
     ON styles.id = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(products.product_desc, 'styles ', ''), ',', numbers.n), ',', -1)
ORDER BY products.id, n

I put a SQL fiddle up with your test data at http://sqlfiddle.com/#!9/863cb/4/0


#4
SELECT s.styleId
     , s.styleText 
  FROM tblproducts AS p 
INNER
  JOIN tbstyles AS s
    ON FIND_IN_SET(s.styleId,p.prodStyle)
 WHERE /* my product query here */ 
GROUP 
    BY s.styleId
     , s.styleText 
ORDER 
    BY s.styleText ASC

#5

See? I told you @r937 would know a more elegant method.

One caveat on that method from when I tried. If the comma delimited string isn’t exact, the FIND_IN_SET won’t work as expected, so you may need to do some string manipulation before putting it in the FIND_IN_SET.

So, for example, if for product_id 1, the styles field is “1, 3” instead of “1,3”, then 1 will match but not 3. You would need to alter the query to

SELECT s.styleId
     , s.styleText 
  FROM tblproducts AS p 
 INNER JOIN tbstyles AS s ON FIND_IN_SET(s.styleId, REPLACE(p.prodStyle, " ", "")) 
 WHERE /* my product query here */ 
 GROUP BY s.styleId , s.styleText 
 ORDER BY s.styleText ASC

#6

Hi,

Thank you for taking the time to reply to my query.

I think my explanation wasn’t as clear as it should have been as the result of your query isn’t what I was expecting as it’s only returning one “style” regardless of other queries.

This query is to populate a “filtering” list - so as other filters/queries reduce the number of possible products this query should just list the styles left in the remaining products, for example

Products:
Product ID 1 - styles 1,3
Product ID 2 - styles 4
Product ID 3 - styles 3,5
Product ID 4 - styles 2,5

If the other product queries mean that only product ID 1 & 3 show then the list of styles would be: 1,3,5
If the other product queries mean that only product ID 3 & 4 show then the list of styles would be: 2,3,5
If the other product queries mean that only product ID 2 shows then the list of styles would be: 4
If the other product queries mean that all products show then the list of styles would be: 1,2,3,4,5
and so on…

so the query needs to show all unique styles left in the remaining products

Many thanks.
Scim


#7

Hi,

Thanks for the reply. Unfortunately the list of possible “style ID’s” is fairly unlimited/dynamic so I wouldn’t be able to hard code the style ID’s in the query.

Regards,
Scim


#8

Hi,

Many thanks, point taken.


#9

please prepare some test data for us

you can use mysqldump for this purpose

please give CREATE TABLE statements, as well as INSERT INTO statements with enough data for a non-trivial test

also, please provide sample code for “/* my product query here */”


#10

Hi,

The products table is actually a list of wines and the “style” is actually grape varieties. Each wine can have several grape varieties (hence the comma separated list) - the “style” table is actually labelled as tblfiltergrapes.

Here’s some sample data for the products table and the filtering table (tblfiltergrapes): (the attached file is a sql dump file renamed as a txt file)

filtering.txt (4.5 KB)

Alternatively here is the dump code:

-- --------------------------------------------------------
-- Host:                         192.168.0.40
-- Server version:               5.5.16 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             9.5.0.5196
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping structure for table winedb.tblfiltergrapes
CREATE TABLE IF NOT EXISTS `tblfiltergrapes` (
  `fgId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fgMenuText` varchar(100) DEFAULT NULL,
  `fgLinkText` varchar(100) DEFAULT NULL,
  `fgPageTitle` varchar(100) DEFAULT NULL,
  `fgEnabled` varchar(1) DEFAULT 'Y',
  `fgImage` varchar(100) DEFAULT NULL,
  `fgDesc` text,
  `fgBrowserTitle` varchar(100) DEFAULT NULL,
  `fgMetaKeywords` text,
  `fgMetaDescription` text,
  `fgSort` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`fgId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- Dumping data for table winedb.tblfiltergrapes: ~5 rows (approximately)
/*!40000 ALTER TABLE `tblfiltergrapes` DISABLE KEYS */;
REPLACE INTO `tblfiltergrapes` (`fgId`, `fgMenuText`, `fgLinkText`, `fgPageTitle`, `fgEnabled`, `fgImage`, `fgDesc`, `fgBrowserTitle`, `fgMetaKeywords`, `fgMetaDescription`, `fgSort`) VALUES
	(1, 'Cabernet Sauvignon', 'Cabernet Sauvignon', 'Cabernet Sauvignon', 'Y', '', '', 'Cabernet Sauvignon', '', '', 1),
	(2, 'Merlot', 'Merlot', 'Merlot', 'Y', '', '', 'Merlot', '', '', 2),
	(3, 'Cabernet Franc', 'Cabernet Franc', 'Cabernet Franc', 'Y', '', '', 'Cabernet Franc', '', '', 3),
	(4, 'Petit Verdot', 'Petit Verdot', 'Petit Verdot', 'Y', '', '', 'Petit Verdot', '', '', 4),
	(5, 'Carmenère', 'Carmenère', 'Carmenère', 'Y', '', '', 'Carmenère', '', '', 5);
/*!40000 ALTER TABLE `tblfiltergrapes` ENABLE KEYS */;

-- Dumping structure for table winedb.tblproducts
CREATE TABLE IF NOT EXISTS `tblproducts` (
  `prodId` int(11) NOT NULL AUTO_INCREMENT,
  `prodTitle` varchar(100) DEFAULT NULL,
  `prodMPN` varchar(100) DEFAULT NULL,
  `prodCountry` int(11) DEFAULT '0',
  `prodRegion` int(11) DEFAULT '0',
  `prodColour` int(11) DEFAULT '0',
  `prodGrapeVar` longtext,
  `prodEnabled` varchar(1) DEFAULT 'Y',
  PRIMARY KEY (`prodId`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

-- Dumping data for table winedb.tblproducts: ~22 rows (approximately)
/*!40000 ALTER TABLE `tblproducts` DISABLE KEYS */;
REPLACE INTO `tblproducts` (`prodId`, `prodTitle`, `prodMPN`, `prodCountry`, `prodRegion`, `prodColour`, `prodGrapeVar`, `prodEnabled`) VALUES
	(1, 'Château Labégorce Zédé', 'BORD0033', 3, 4, 1, '1,2,3,4', 'Y'),
	(2, 'La Dame de Montrose', 'BORD0032', 3, 4, 1, '1,2,3', 'Y'),
	(3, 'Château Smith Haut Lafitte', 'BORD0647', 5, 0, 1, '1,2,3', 'Y'),
	(4, 'Château Talbot', 'BORD0668', 3, 4, 1, '1,2,3,4', 'Y'),
	(5, 'Château Troplong Mondot', 'BORD0662', 3, 4, 1, '1,2,3', 'Y'),
	(6, 'Vieux Château Mazerat', 'BORD08108', 3, 4, 1, '2,3 ', 'Y'),
	(7, 'Château Aiguilhe', 'BORD0986', 5, 0, 1, '2,3', 'Y'),
	(8, 'Château Branaire Ducru', 'BORD0919', 3, 4, 2, '1,2,3,4', 'Y'),
	(9, 'Château Canon', 'BORD0966', 3, 4, 2, '2,3', 'Y'),
	(10, 'La Croix de Beaucaillou', 'BORD1921', 3, 4, 2, '1,2', 'Y'),
	(11, 'Château La Dominique', 'BORD0969', 3, 4, 2, '2,3,1', 'Y'),
	(12, 'Château Nénin', 'BORD 09139', 3, 4, 2, '2,3,1', 'Y'),
	(13, 'Château Potensac', 'BORD0957', 3, 4, 2, '1,2,3', 'Y'),
	(14, 'Pagodes de Cos', 'BORD0902', 3, 4, 2, '1,2,3,4', 'Y'),
	(15, 'Château de Fonbel', 'BORD1048', 5, 0, 3, '1,2,4,5', 'Y'),
	(16, 'Château Fourcas Hosten', 'BORD1060', 5, 0, 3, '1,2,3', 'Y'),
	(17, 'Château de Fonbel', 'BORD1192', 5, 0, 3, '1,2,4,5', 'Y'),
	(18, 'Château Lilian Ladouys', 'BORD11150', 5, 0, 3, '1,2,3', 'Y'),
	(19, 'Château Canon La Gaffelière', 'BORD1341', 5, 0, 3, '1,2,3', 'Y'),
	(20, 'Château Talbot', 'BORD13160', 6, 0, 3, '3,4', 'Y'),
	(21, 'Château Montrose', 'BORD14131', 6, 0, 3, '5', 'Y'),
	(22, 'La Mission Haut Brion', 'BORD14175M', 6, 0, 3, '4,5', 'Y');
/*!40000 ALTER TABLE `tblproducts` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Putting these table names into the sample code you wrote in the earlier post would show as:

SELECT f.fgId
     , f.fgPageTitle
  FROM tblproducts AS p 
INNER
  JOIN tblfiltergrapes AS f
    ON FIND_IN_SET(f.fgId,p.prodGrapeVar)
 WHERE prodCountry = 3 AND prodColour = 1
GROUP 
    BY f.fgId
     , f.fgPageTitle
ORDER 
    BY f.fgPageTitle ASC

this query should return the following list:

fgId 1 - Cabernet Sauvignon
fgId 2 - Merlot
fgId 3 - Cabernet Franc
fgId 4 - Petit Verdot

whereas changing the product query to: WHERE prodCountry = 6 AND prodColour = 3 would return the following:

fgId 3 - Cabernet Franc
fgId 4 - Petit Verdot
fgId 5 - Carmenère

and changing the product query to: WHERE prodCountry = 5 AND prodColour = 1 would return the following:

fgId 1 - Cabernet Sauvignon
fgId 2 - Merlot
fgId 3 - Cabernet Franc

Hope this all makes sense.

Many thanks,
Scim


#11

i tested it on each of the three conditions, and the query ~does~ return what it’s supposed to

any further questions?


#12

Agreed. The SQL statement in your post returns exactly what you said it should, with the exception of the order, which you defined in the sql statement as being by fgPageTitle, and in your test results, it’s by fgId


#13

Sorry, sorry, sorry, sorry, sorry and sorry again. I had a mistake in my code, I was referencing the wrong field in the “FIND_IN_SET” clause which was causing the spurious result. Your code works perfectly.

I’m so sorry for wasting your time and I’m very grateful that you took the time to check things with live data. I will check, double check and triple check my code next time before posting.

Thanks again (and sorry again).


#14

no problem, i was happy to do it (and confirm for myself that my off-the-top-of-my-head sql actually works)