SELECT [u].*, [user_roles].[roleid], [roles].[rolecode]
FROM [users] AS [u]
INNER JOIN [user_roles] ON [user_roles].[userid] = [u].[userid]
LEFT JOIN [roles] ON [user_roles].[roleid] = [roles].[roleid]
WHERE [*user_roles.roleid*] = (SELECT [roleid] FROM [roles] WHERE
[rolecode] = 'SYSADMIN')
that bolded syntax is wrong...it (for sql server) needs to be
[user_roles].[roleid]...and here is my builder code...
local.queryresult = LOCAL.query2.from("users u")
.select("u.*, user_roles.roleid, roles.rolecode")
.join("user_roles", "user_roles.userid", "u.userid")
.leftjoin("roles", "user_roles.roleid","roles.roleid")
.where("user_roles.roleid","=",function(q) {
q.select("roleid").from("roles").where("rolecode","SYSADMIN");
})
.get();
with no alias in particular (though I tested the same build with all
aliased tables and field references), I would have expected it to create
the proper syntax in the where clause because it does everywhere else. Is
this a bug or is there something wrong with my build? Query works fine
with the right syntax in place tested in the sql manager, but nothing I do
in the builder seems to work, including
.where("[user_roles].[roleid]".etc). Perhaps a different type of where()
function is needed in this kind of instance? Still pretty new to the QB
tooling but I did not really see one or a sample using joins and a where
clause...hopefully I just overlooked something.
Thanks,
Mike