|
Posted by Matt C on September 16, 2006, 1:06 pm
Please log in for more thread options strawberry wrote:
> Matt C wrote:
>> strawberry wrote:
>>> Matt C wrote:
>>>> I have rather complicated join I can't figure out (been mostly using
>>>> trial-and-error method...). I'll just explain with words first: (table
>>>> layout below)
>>>>
>>>> Users are in categories. Depending on what category they're in, they
>>>> have a different set of functions that are *possible. There's an xref
>>>> table that defines which functions they actually have.
>>>>
>>>> So e.g. user 1 is in category 4, and thus *might possess functions 6,7,
>>>> and 8. In fact she only possesses 7, so there's an xref record:
>>>> user_id | function_id
>>>> 1 | 7
>>>>
>>>> My query needs to loop over categories, and for each user in a category
>>>> tell me which functions she qualifies for but does not possess, e.g:
>>>> user_id | function_id | result
>>>> 1 | 6 | NULL
>>>> 1 | 7 | 1 (or "sure thing" or whatever)
>>>> 1 | 8 | NULL
>>>>
>>>> i.e. if there's no record in the xref table with user_id 1 and
>>>> function_id 6, show NULL.
>>>>
>>>> One thought I had would be just to have an "available" field in the xref
>>>> table, with values 'y' or 'n'. But in this case every user would need
>>>> (in this example) 3 records in the xref table, and that struck me as
>>>> inefficient.
>>>>
>>>> users
>>>> ------
>>>> user_id
>>>> name
>>>> category_id
>>>>
>>>> categories
>>>> ----------
>>>> user_id
>>>> category_name
>>>>
>>>> functions
>>>> ---------
>>>> function_id
>>>> category_id
>>>> function_name
>>>>
>>>> xref
>>>> -----
>>>> user_id
>>>> function_id
>>> can functions belong to more than one category? the functions table
>>> suggests that they can't but I just wanted to check
>>>
>> They cannot. The only many-to-many rel. is users_functions
>
> How about:
>
> SELECT *
> FROM (
> SELECT u.user_id, u.name, u.category_id, f.function, x.function_id
> FROM users u
> LEFT JOIN functions f ON f.category_id = u.category_id
> LEFT JOIN xref x ON x.user_id = u.user_id
> AND x.function_id = f.function_id
> )t1
> WHERE ISNULL( t1.function_id )
>
Thanks, I'll let you know in a bit.
| 
XML Sitemap