I'm stumped by this query that I think will involve multiple self joins and left joins.
My data looks like: +------+----------+------------+ | id | Category | description | +------+----------+------------+ | 1 | color | red | | 2 | color | blue | | 3 | color | yellow | | 4 | size | small | | 5 | size | large | +------+----------+------------+ I want to permute every combination of description by Category. In the simple case above I can do select color.description , size.description from mystats as color, mystats as size where color.Category="color" and size.Category="size"; +-------------+-------------+ | description | description | +-------------+-------------+ | red | small | | blue | small | | yellow | small | | red | large | | blue | large | | yellow | large | +-------------+-------------+ This works fine as long as there are entries present for each category. The number of categories is not arbitrary (eventually there will be exactly 14 categories), but there may not be entries in the data table for all categories. For example, there is a third category "material" but there are no values... so if I had extended my query above to select color.description , size.description, material.description from mystats as color, mystats as size, mystats as material where color.Category="color" and size.Category="size" and material.Category="material"; I get no records. what I want would be something like this: +-------------+-------------+-------------+ | description | description | description | +-------------+-------------+-------------+ | red | small | NULL | | blue | small | NULL | | yellow | small | NULL | | red | large | NULL | | blue | large | NULL | | yellow | large | NULL | +-------------+-------------+-------------+ where the third column is null because there are no material categories in the data. I think I need some sort of left join here but in the general case I don't think it will work because whatever I decide is my "leftmost" Category may have no entries in the table. I know I can do this at the application level with a few seperate queries and a little more processing, but I'm sure that this can be done with plain SQL and I'd like to learn how. As usual, any help is appreciated. Thanks! Gary H -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
