- 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]
