Advanced SQL INSERT

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?

From stackoverflow

• 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
  

• 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.

• 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.