Help w/ a query 2

[ryanrat]

Hello all,

I have a query that I have been using in Microsoft Access that doesn't work with mySQL. I was wondering if someone could help me convert this:

SELECT user.usrID, user.ctGiven, user.ctSurname,user.coName
FROM user
WHERE user.usrID NOT IN(
SELECT user.usrID
FROM user
INNER JOIN permissions on user.usrID = permissions.usrID
WHERE permissions.jobNumber = #attributes.jobNumber#)
AND user.permissions='user'
ORDER BY
user.ctSurname

into a mySQL compatible query.

 

[petersJazz]

Try something like this:

SELECT user.usrID,
       user.ctGiven,
       user.ctSurname,
       user.coName
FROM   user outher join permissions on (user.usrID = permissions.usrID and permissions.jobNumber = #attributes.jobNumber#)
WHERE  permissions.<<some column>> is null
AND    user.permissions='user'
ORDER BY user.ctSurname

 

[ryanrat]

Thanks for the reply.

I get the following error message (I assumed you meant "outer join" rather than "outher join&quot:

TCX][MyODBC]You have an error in your SQL syntax near 'outer join permissions on (user.usrID = permissions.usrID and permissions.jobNum' at line 5

Any suggestions?

 

[piti]

there's no simple outer join in mysql, you have to use LEFT or RIGHT outer join

look here for join syntax

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IDX1401

 

[ryanrat]

I tried using this query:

SELECT user.usrID,
user.ctGiven,
user.ctSurname,
user.coName
FROM user left outer join permissions on (user.usrID = permissions.usrID and permissions.jobNumber = #attributes.jobNumber#)
WHERE permissions.usrID is null
AND user.permissions = 'user'
ORDER BY user.ctSurname

It appears to give the results I am seeking.