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

[Matt C]

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

[strawberry]

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

[Matt C]

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

[strawberry]

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 )

[Matt C]

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.

[Matt C]

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.

[strawberry]

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

[Matt C]

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.

[strawberry]

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.