Help with a query

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

 

[tlbroadbent]

You'll probably get quicker answers to a MySQL question in the MySQL forum. Here's my suggestion... using a LEFT JOIN.

SELECT
user.usrID, user.ctGiven,
user.ctSurname,user.coName
FROM user
LEFT JOIN
(User
INNER JOIN permissions
ON user.usrID = permissions.usrID
WHERE permissions.jobNumber = #attributes.jobNumber#
AND user.permissions='user') As qry
ON user.userid = qry.userid
WHERE qry.userid IS NULL
ORDER BY user.ctSurname
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.