test your intermediate SQL (joins) by fixing my problem !

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

Re: test your intermediate SQL (joins) by fixing my problem !

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

Re: test your intermediate SQL (joins) by fixing my problem !

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

Re: test your intermediate SQL (joins) by fixing my problem !

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 )

Re: test your intermediate SQL (joins) by fixing my problem !

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.

Re: test your intermediate SQL (joins) by fixing my problem !

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. You got a wishlist somewhere?

I had to change
WHERE ISNULL( t1.function_id )
to simple
ORDER by user_id

to get what I wanted, which is a sorted set of all matching functions,
with NULLs when "unavailable". But joins are perfect.

Re: test your intermediate SQL (joins) by fixing my problem !

Matt C wrote:
> 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. You got a wishlist somewhere?
>
> I had to change
> WHERE ISNULL( t1.function_id )
> to simple
> ORDER by user_id
>
> to get what I wanted, which is a sorted set of all matching functions,
> with NULLs when "unavailable". But joins are perfect.

ha - i just enjoy the challenge but, since you ask:

http://www.amazon.co.uk/gp/registry/1D7R707SQRFSD

anyway, glad if it helped

Re: test your intermediate SQL (joins) by fixing my problem !

strawberry wrote:
> Matt C wrote:
>> 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. You got a wishlist somewhere?
>>
>> I had to change
>> WHERE ISNULL( t1.function_id )
>> to simple
>> ORDER by user_id
>>
>> to get what I wanted, which is a sorted set of all matching functions,
>> with NULLs when "unavailable". But joins are perfect.
>
> ha - i just enjoy the challenge but, since you ask:
>
> http://www.amazon.co.uk/gp/registry/1D7R707SQRFSD
>
> anyway, glad if it helped
>

Ukrainian tractors? I couldn't resist that one. You're weird, dude.

Re: test your intermediate SQL (joins) by fixing my problem !

Matt C wrote:
> strawberry wrote:
> > Matt C wrote:
> >> 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. You got a wishlist somewhere?
> >>
> >> I had to change
> >> WHERE ISNULL( t1.function_id )
> >> to simple
> >> ORDER by user_id
> >>
> >> to get what I wanted, which is a sorted set of all matching functions,
> >> with NULLs when "unavailable". But joins are perfect.
> >
> > ha - i just enjoy the challenge but, since you ask:
> >
> > http://www.amazon.co.uk/gp/registry/1D7R707SQRFSD
> >
> > anyway, glad if it helped
> >
>
> Ukrainian tractors? I couldn't resist that one. You're weird, dude.

:-)

You know, in years to come digital archaeologists may struggle to
follow the thread of this conversation.

Thank you, it's very thoughtful.