mySQL structure & query problem

[bbarby]

Hey All,

I've run up against a brick wall... I'm trying to set up a db to store hex values & colour combinations. The origonal information is based on breaking the colour groupings down by impact (happy, angry, etc). Each hex value is assigned a unique number (1 for 000000, 2 for 000033, 216 for FFFFFF). There are three colours per combination. 45 combinations per grouping & a total of about 20 groupings.

The hex values are stored in one table. The group names (happy, angry) are stored in another, & the combinations are stored in yet another table. The table structures are as such:

HEX
hexID
hexvalue

GROUPING
groupingID
grouping

COMBINATION
combiID
gropuingID
colourA
colourB
colourC

The idea behind the query is that the comination is called, & it gets the hex values for colourA, colourB, & colourC.
Problem 1: When I run a query on this basis, I can only seem to associate the hex value with colourA. I can't seem to make the query bring up hex values for colours B&C.

So I added a couple of tables, HEXB & HEXC. The new query looks like this:

SELECT combiID,hexa, hexb, hexc

FROM combi, hexa, hexb, hexc, grouping

WHERE combi.grouping = '" . ($variable) . "'
AND hexa.hexID=colourA
AND hexb.hexID=colourB
AND hexc.hexID=colourC
AND combi.grouping=grouping.groupingID

ORDER BY combiID ASC

When there's only 125 combinations, it works fine, but at 350 combinations, the query is incredibly slow. & if there are a full 45 combinations in a grouping, the query tosses a 'table is full' error. The mySQL version is 3.23.39.

Any ideas?

TIA

Benjamin

[[deleted]]

First of all, you can JOIN with the same table more than once by using an alias:

FROM hex hexa, hex hexb, hex hexc;

Now the same 'hex' table is available three times, as hexa hexb and hexc

Second, shouldn't this:

AND combi.grouping=grouping.groupingID

be this:
AND combi.groupingID=grouping.groupingID

the error you get suggests that the query results in a 'cartesian product', where every record in one table is matched once with every record in every other table, giving trillions of records.