Page 1 of 2
Mysql mindmelt...
Posted: Tue Sep 02, 2008 3:33 pm
by dwimberley
This is my first time posting on a forum, but I'm about to lose my mind if I don't get this figured out. I've built a search engine that allows multiple selections from several search criteria. The following sql results in no matches, though it should:
Code: Select all
select DISTINCT products.* from products, color, products_color where ((color.color_id = '11' and products_color.color_id = color.color_id and products_color.product_id = products.product_id) and (color.color_id = '21' and products_color.color_id = color.color_id and products_color.product_id = products.product_id))
The tables/fields referenced in the query above are explained here:
products = product_id, product_name, product_type, etc. (multiple characterstics of individual product, keyed by product_id);
color = color_id, color_name;
product_color = product_id, color_id
This example is basically supposed to match results where the color_id is both 11 and 21 (in separate rows of the products_color table), but this query actually is checking the same row for both color_id's and getting nothing back, since each row only stores one color_id. I suspect that using some sort of subquery might be the solution, but I'm not sure.
Any help is greatly appreciated! Hope this all makes sense.
Re: Mysql mindmelt...
Posted: Tue Sep 02, 2008 3:39 pm
by jaoudestudios
I have only had a quick look, but I think you should remove your DISTINCT, or atleast apply it to a specific row not the entire table.
i.e. instead of DISTINCT products.* do DISTINCT products.xxxxxxx the column you want distinct or use GROUP BY.
Try that and let me know how you get on. If you are still having problems I will take a closer look.
Re: Mysql mindmelt...
Posted: Tue Sep 02, 2008 3:43 pm
by dwimberley
I gave that a shot and still no results on a search using 2 color_id's. However, it did effect my results when only one color_id is used (it actually doubled the results by listing them twice...). I should probably look at a better way to retrieve unique product_id's, but for now that seems like another story for another day.
Thanks for the suggestion.
Re: Mysql mindmelt...
Posted: Tue Sep 02, 2008 5:18 pm
by andyhoneycutt
Maybe this will work?
Code: Select all
SELECT p.*
FROM products p
JOIN products_color pc ON pc.product_id = p.product_id
JOIN color c ON c.color_id = pc.color_id
WHERE c.color_id IN ('11','21');
Re: Mysql mindmelt...
Posted: Tue Sep 02, 2008 5:41 pm
by dwimberley
I'll give that a shot in the morning. Thanks for the help!
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 7:39 am
by dwimberley
Okay, tried the suggested joined query above, but it basically seemed to gather all the products joined to each color_id. What I'm trying to arrive at is only products that have both color_id's assigned to them.
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 9:33 am
by andyhoneycutt
Code: Select all
SELECT p.*
FROM products p
JOIN products_color pc ON pc.product_id = p.product_id
JOIN color c ON c.color_id = pc.color_id
WHERE pc.color_id IN ('11','21');
Try this instead. If that fails, any chance I can get your tables to test this on my end?
-Andy
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 10:04 am
by dwimberley
Hmmm. If there's a difference between that query and the one from yesterday, I'm not seeing it. Tried it anyway and same results. Not sure how to give you my tables... do you just want an sql export file from my db?
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 10:13 am
by andyhoneycutt
The difference is that I'm pulling the where clause against the product_colors table instead of colors.
run a "describe <tablename>" for each table you have and copy the output here. If you are using phpMyAdmin, go to "export", select your database, use "SQL" as the output format.
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 10:27 am
by dwimberley
Okay; here's the table structure data (didn't bother with the actual data dump, since there are tens of thousands of rows of data in the products and products_color tables. Hope this helps!
Thanks!
--
-- Table structure for table `color`
--
CREATE TABLE IF NOT EXISTS `color` (
`color_id` int(10) unsigned NOT NULL default '0',
`color_name` varchar(45) NOT NULL,
`company_id` varchar(3) NOT NULL,
`application` varchar(1) NOT NULL,
PRIMARY KEY (`color_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
`product_inc` int(7) NOT NULL auto_increment,
`product_id` int(7) unsigned zerofill NOT NULL,
`product_name` varchar(45) NOT NULL,
`product_color` varchar(45) default NULL,
`product_image` varchar(100) default NULL,
`width` float(4,2) default NULL,
`vert_repeat` float(4,2) default NULL,
`horz_repeat` float(4,2) default NULL,
`product_description` text,
`company_id` char(3) default NULL,
`collection_id` int(10) default NULL,
`book_id` int(11) NOT NULL,
`product_scale_id` varchar(1) default NULL,
`application_id` varchar(1) NOT NULL,
`published_stat` varchar(3) NOT NULL default '0',
`visibility_stat` varchar(3) NOT NULL,
PRIMARY KEY (`product_inc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=293140 ;
-- --------------------------------------------------------
--
-- Table structure for table `products_color`
--
CREATE TABLE IF NOT EXISTS `products_color` (
`product_id` int(7) unsigned zerofill NOT NULL default '0000000',
`color_id` int(10) NOT NULL,
`color_rank` varchar(5) default NULL,
KEY `FK_tbl_product_color_xref_1` (`product_id`),
KEY `FK_tbl_product_color_xref_2` (`color_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 11:13 am
by andyhoneycutt
Well I definitely understand what you're trying to aim for now. See if the following works for you:
Code: Select all
SELECT * FROM products
JOIN (SELECT product_id,
COUNT(product_id) AS pcolor_count
FROM products_color
WHERE color_id IN (21,11)
GROUP BY product_id
) c ON c.product_id = products.product_id
WHERE pcolor_count > 1;
-Andy
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 11:35 am
by dwimberley
Okay, when I run that I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pc.color_id'
However, I have made a slight modification to the sql which may have caused the problem (I'm having to add a couple more limiters to refine the search). Here's the sql after I made my changes:
$sql_search = "SELECT * FROM products JOIN (SELECT product_id,
COUNT(product_id) AS pcolor_count
FROM products_color
WHERE color_id IN (21,11)
GROUP BY product_id
) c ON c.product_id = products.product_id
WHERE pcolor_count > 1 pc.color_id and products.application_id = 'F' and products.company_id = '01'";
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 11:41 am
by andyhoneycutt
Code: Select all
$sql_search = "SELECT * FROM products JOIN (SELECT product_id,
COUNT(product_id) AS pcolor_count
FROM products_color
WHERE color_id IN (21,11)
GROUP BY product_id
) c ON c.product_id = products.product_id
WHERE pcolor_count > 1 AND products.application_id = 'F' AND products.company_id = '01'";
Just get rid of the pc.color_id statement. should be good. lemme know if that works for you.
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 11:45 am
by dwimberley
Unknown column 'pc.color_id' in 'where clause'
Looks like it just isn't making the link between pc and products_color since the alias hasn't been assigned?
Re: Mysql mindmelt...
Posted: Wed Sep 03, 2008 11:55 am
by andyhoneycutt
See my previous post: the pc.color_id statement should be removed.
-Andy