[SOLVED] 'Intersect' style query

[asmith3006]

Hi,

 

I have a table used for indexing categories of products like:

Product | Category
--------|---------
ABC      | Plastic
ABC      | Red
ABC      | Tall
123      | Plastic
123      | Blue
123      | Tall

 

Now, how can I select all the tall & plastic products? This table could grow quite large so I want to keep this as efficient as possible.

 

I thought that some kind of intersect query would work, but mySQL doesn't have them apparently

 

One more complication to throw in is that I don't know how many categories there will be. I may need to search for one or five or.....

 

 

 

Any and all help much appreciated.

 

Thanks.

 

Andrew.

[Mchl]

SELECT * FROM table WHERE Category = 'Plastic' OR Category = 'Tall';

 

or

 

SELECT * FROM table WHERE Category IN ('Plastic','Tall');

 

Also create an index on Category column.

[asmith3006]

Wont that simply give me all the products that are EITHER tall or plastic? I want products which are BOTH tall AND plastic.

 

e.g. if I were to search for a tall AND blue product I only want to find 123 but if I search for a tall AND plastic product I want ABC AND 123.

 

Thanks.

[HuggieBear]

AND is more restrictive than OR as it requires more conditions to be met.  OR will give you the larger recordset based on the same condition.

[Mchl]

I see

 

That's what I've come up with... probably there's better way though

 

SELECT product,  GROUP_CONCAT(category ORDER BY category) AS c FROM pc GROUP BY product HAVING c LIKE '%Blue%' AND c LIKE '%Tall%'

[asmith3006]

Is that more efficient than doing

SELECT product FROM product_table WHERE category = 'tall' IN (SELECT product FROM product_table WHERE category = 'plastic');?

[Mchl]

I'm not sure. But if you say you need to search on multiple categories, nested subqueries might get really slow... Not to mention tricky to create.

[fenway]

You may want to read this thread.

 

SELECT pc.product
FROM pc as t1
CROSS JOIN pc as t2 USING ( id )
WHERE pc.category = 'plastic' and pc.category = 'tall'

 

Obviously, if you don't have an id field, you should use USING ( product, category ) -- and have a combined index.

 

Hope that helps.

[Mchl]

0 rows fetched...

 

I think you meant something like this:

 

SELECT product 
FROM pc AS t1 
CROSS JOIN pc AS t2 USING (product) 
WHERE t1.category = "Tall" AND t2.category = "Blue"

[fenway]

0 rows fetched...

 

I think you meant something like this:

 

SELECT product 
FROM pc AS t1 
CROSS JOIN pc AS t2 USING (product) 
WHERE t1.category = "Tall" AND t2.category = "Blue"

Yes, sorry, good catch -- it's really late in EST.

[Mchl]

Still I'm ashamed that I didn't learn from the topic mentioned...

[kickstart]

Hi

 

Another option might be

 

SELECT a.product
FROM table a
JOIN table b ON a.product = b.product
WHERE a.category = 'Plastic'
AND b.category = 'Tall'

 

Would be easy to add more joins (and where clauses to go with them) if there are more required categories.

 

All the best

 

Keith

[Mchl]

That's exactly same query. JOIN, CROSS JOIN and INNER JOIN are synonymic for MySQL

[kickstart]

Hi

 

Sorry, I have had too many varieties of SQL inflicted on me over time!

 

All the best

 

Keith

[Mchl]

We all wish for standard implementations

[kickstart]

Hi

 

Worst is when you are using different standards at the same time on different projects.

 

Even M$ cannot manage to have an SQL standard between sql server and access.

 

All the best

 

Keith

[asmith3006]

Oooohh I like the look of the JOIN one. I will try that.

 

Thanks a lot

[asmith3006]

You may want to read this thread.

 

SELECT pc.product
FROM pc as t1
CROSS JOIN pc as t2 USING ( id )
WHERE pc.category = 'plastic' and pc.category = 'tall'

 

Obviously, if you don't have an id field, you should use USING ( product, category ) -- and have a combined index.

 

Hope that helps.

Humm... you say 'obviously'.... I don't really understand multi-field keys. Can you elaborate a bit please?

[fenway]

Sorry, Mchl caught an earlier mistake... you only need a key on the column you're joining.