[Paul Boreham]

Right - gonna try again and close my previous post as it was too confusing!

Simple explanation...

====

I need to query a table to get all the records out that have 2 or more matching values in the same field.

So in english...

"Show me all records that have an ATTRIBUTE of "red" AND an ATTRIBUTE of "large"

So for example, if Product A is red and large, it would show. If product B is large and blue, it wouldnt show.

====

Is that possible in any way? Tried JOINS, UNIONS etc though I'm not advanced enough in mysql to fully understand it.

Thanks for any help/suggestions.

[morgano]

$query="SELECT * FROM table WHERE ATTRIBUTE='red' AND ATTRIBUTE='large'";

[Paul Boreham]

morgano, on 04 August 2009 - 02:17 PM, said:

$query="SELECT * FROM table WHERE ATTRIBUTE='red' AND ATTRIBUTE='large'";

Thanks, but that would fail.

ATTRIBUTE is 1 column - so (in this context) in cannot be red AND large - its not possible.

This feature is possible though as it works on Ebay, Amazon, Halfords etc.

[scaz182]

Its a little hard to help without knowing the structure of your database but assuming the required data is in just one table surely you can just to this:

SELECT * FROM table1 WHERE attribute_1 = 'red' AND attriute_2 = 'large'

[scaz182]

Paul Boreham, on 04 August 2009 - 02:21 PM, said:

Thanks, but that would fail.

ATTRIBUTE is 1 column - so (in this context) in cannot be red AND large - its not possible.

This feature is possible though as it works on Ebay, Amazon, Halfords etc.

opps just saw this:

Surely you shouldn't have a database with a field that can contain multiple values (i.e. large or small and red or blue) you need to normalise it.

[NeRo]

Is this not a case for using aliases?

SELECT * 
FROM table AS t1 , table AS t2
WHERE t1.attribute LIKE '%red%' AND t2.attriute LIKE '%large%'

It is ages since I've done this sort of thing, so it's probably wrong, but may point someone in the correct direction

[Paul Boreham]

NeRo, on 04 August 2009 - 03:03 PM, said:

Is this not a case for using aliases?

SELECT * 
FROM table AS t1 , table AS t2
WHERE t1.attribute LIKE '%red%' AND t2.attriute LIKE '%large%'

It is ages since I've done this sort of thing, so it's probably wrong, but may point someone in the correct direction

So, can I call the same table name and give it an alias - i.e.

FROM tableAttribute AS t1 , tableAttribute AS t2

?? Didnt know you could do that if so and may help - thanks!

Thanks for all the other replies above too, really helpful!

[NeRo]

I tweaked the code from yesterday and tried it on one of my databases and got the desired result ..

SELECT  distinct t1.attribute 
FROM `table` AS t1 , `table` AS t2
WHERE t1.attribute LIKE '%red%' and t2.attribute LIKE '%large%'