Mysql mindmelt...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Mysql mindmelt...

Post 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.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Mysql mindmelt...

Post 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.
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post 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');
 
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post by dwimberley »

I'll give that a shot in the morning. Thanks for the help!
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post 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
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post 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.
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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;
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post 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
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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'";
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post 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.
dwimberley
Forum Newbie
Posts: 9
Joined: Tue Sep 02, 2008 2:57 pm

Re: Mysql mindmelt...

Post 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?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Mysql mindmelt...

Post by andyhoneycutt »

See my previous post: the pc.color_id statement should be removed.

-Andy
Post Reply