SQL question (NOT IN)

[adrian_quah]

Guys, what's wrong with this SQL statement?

SELECT Rights_ID FROM rights_description WHERE Rights_ID NOT IN (SELECT Rights_ID FROM groups_access_rights`)

there seems 2 be a syntax error, thanks for ur help again.🙂

[Lars_Berg]

Apart from the tick at the end the query is a valid sql statement.

If you are using Mysql that type of query is not supported so you hav to rewrite it

SELECT rd.Rights_ID 
  FROM rights_description rd left join
groups_access_rights ga on 
rd.Rights_ID = ga.Rights_ID 
WHERE ga.Rights_ID is null

[PHP-Editors_com]

If your using MySQL then you may be surprised to discover that sub queries like this are not supported. You have to provide a comma delimited list on the IN() clause. see below for a example.

SELECT Rights_ID FROM rights_description WHERE Rights_ID NOT IN (2,3,6)

this means you need to get the results by running an other query first. There very well may be a better way to do this though.

[adrian_quah]

Thanks Lars Berg,
yes i am using MySQL...Didnt notice that. but i will be porting my app to MS SQL Server after development. Shd the SQL statement u showed be allright in SQL Server?

thanks again!

[adrian_quah]

I rewrote the SQL to:

SELECT Rights_ID FROM rights_description left join groups_access_rights on rights_description.Rights_ID = groups_access_rights.Rights_ID WHERE groups_access_rights.Rights_ID is null

However an error was returned from phpMyAdmin:

Column: 'Rights_ID' in field list is ambiguous

[Lars_Berg]

Yes, my fault. As the column rights_id is present in both tables it must be qualified.

SELECT rights_description.Rights_ID FROM rights_description left join groups_access_rights 
on rights_description.Rights_ID = groups_access_rights.Rights_ID 
WHERE groups_access_rights.Rights_ID is null

The query should work in MS SQL server (as well as the not in variant).

[adrian_quah]

Thanks again guys! Lars Berg, the SQL statement was fine...Now, i discovered i need something a lil' different since the groups_access_rights.Group_ID is not a primary key, meaning that there can be different values such as 'admin', 'support', 'hr'. The earlier statement would not have returned the results if in rights_description it exists, but in groups_access_rights, the Group_ID exists under a different name, e.g. 'hr'.

I'd like to get rights_description.Rights_ID that exists in rights_description AND groups_access_rights.Group_ID != 'admin'...

Tried:

SELECT rights_description.Rights_ID
FROM rights_description
left join groups_access_rights
on rights_description.Rights_ID = groups_access_rights.Rights_ID
WHERE groups_access_rights.Group_ID != 'admin'
AND groups_access_rights.Rights_ID is null

but not the right one either..gotta learn more on inner, outer, left, right table joins. Thanks for ur help n time. And yeah, Happy Chinese New Year 2 those who celebrate it 😃

[adrian_quah]

SELECT rights_description.Rights_ID
FROM rights_description
left join groups_access_rights
on rights_description.Rights_ID = groups_access_rights.Rights_ID
WHERE groups_access_rights.Rights_ID is null
IF groups_access_rights.Group_ID != 'admin'

what i'd like is somewhat shown in the last sentence, but in correct SQL syntax. Thank you in advance guys!