Advanced SQL INSERT

Question

I have this query that works fine. It selects rows into the SY.UserOptions table for the ‘Jeff’ user.

However, I created another query that I want to do the same thing, but for every user. So I added SY.Users to the query, which in effect mulplies the 2 tables together. However, it gives me an error that I do not understand.

--This works
SELECT ‘Jeff’, t.Application, t.Task, tl.Description
FROM SY.Tasks t
LEFT OUTER JOIN SY.TaskLevels tl 
ON t.Application = tl.Application And t.Task = tl.Task AND t.DftAccessLevel = tl.AccessLevel

-- This does not work
SELECT u.[User], t.Application, t.Task, tl.Description
FROM SY.Tasks t, SY.Users u
LEFT OUTER JOIN SY.TaskLevels tl 
ON t.Application = tl.Application And t.Task = tl.Task AND t.DftAccessLevel = tl.AccessLevel

--Here is the error

Msg 4104, Level 16, State 1, Procedure CreateUserOptions, Line 15 The multi-part identifier "t.Application" could not be bound. Msg 4104, Level 16, State 1, Procedure CreateUserOptions, Line 15 The multi-part identifier "t.Task" could not be bound. Msg 4104, Level 16, State 1, Procedure CreateUserOptions, Line 15 The multi-part identifier "t.DftAccessLevel" could not be bound.

Can I not multiply tables together like that and include a join?

Answer 1

You need a field to join the users table to the Tasks table.

SELECT u.[User], t.Application, t.Task, tl.Description
FROM SY.Tasks t
INNER JOIN SY.Users u --LEFT OUTER if it makes a difference
  ON t.user = u.User --not sure if these fields are available maybe some type of userId?
LEFT OUTER JOIN SY.TaskLevels tl 
  ON t.Application = tl.Application 
  And t.Task = tl.Task AND t.DftAccessLevel = tl.AccessLevel

Answer 2

It's because you're joining USERS to TaskLevels instead of Tasks to TaskLevels.

Try This:

SELECT u.[User], t.Application, t.Task, tl.Description
FROM SY.Users u, SY.Tasks t
LEFT OUTER JOIN SY.TaskLevels tl ON t.Application = tl.Application And t.Task = tl.Task AND t.DftAccessLevel = tl.AccessLevel

This will give you the cartesian product of users with (Tasks TaskLevels) though. So every user will have every task.

Answer 3

I think the problem is that in the second query, when you join the SY.Users table and the SY.TaskLevels table, you are referencing the SY.Tasks table - which is not part of the join.

Switch the Sy.Users table and the Sy.Tasks table around and your problem should be fixed.