----- Original Message ----- From: "Gary Huntress" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 12:14 PM Subject: Multiple Self Joins and Left Joins ?
> > 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 | > +------+----------+------------+ Are you sure this is a good way to set up your data model? I don't have the time to look everything up, but I would question this design because there's a lot of redundancy going on. That is, while I'm not sure it violates any normal form, clearly there's a "functional dependency" (doubt I'm using that term correctly as per its definition in my RDB text): e.g. if description = red, then category = color. So "category" seems redundant. > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
