Simple SQL Query?

SQL Query help using PHP and MSSQL

[The_Warden]

Hi. I'm having some issues with a query that I require. In my mind it should be simple but for the life of me I can not figure out how to go about doing it. The below query, queries a MSSQL database and returns what security group a web account user is in.

Now I need another query that will return the opposite of this. Web Accounts that are not in a security group (tblWebAccountGroup.Web_Account_Group_Name_ID!= var). Sounds simple to me and possible using an SQL query. At present I've done such queries in PHP by writing two queries and doing a data comparison and return what is different between the two queries. As I'm sure you realize this is a major amount of coding to do this manually. How can I do this using SQL? Any help would be greatly appreciated.

BTW, I'm using IIS v5.0.2195.3649 with PHP v4.2.3 and MSSQL Server 2000 (v8.00.760 Intel X86). If you need any table schemas or other information please let me know.

$szQry = "SELECT tblWebAccounts.People_ID, viewPeople.First_Name, viewPeople.Last_Name
FROM tblWebAccounts, viewPeople, tblWebAccountGroup
WHERE tblWebAccountGroup.Web_Account_Group_Name_ID=$_POST[nSecurityGroup]
AND tblWebAccounts.People_ID=viewPeople.People_ID
AND tblWebAccounts.Web_Account_ID=tblWebAccountGroup.Web_Account_ID
ORDER BY viewPeople.First_Name, viewPeople.Last_Name";

[flashman]

This seems to be simple.
If case is true
then
not case is false

i.e.
where tblWebAccountGroup.Web_Account_Group_Name_ID=$_POST[nSecurityGroup]

is users from security group

and where tblWebAccountGroup.Web_Account_Group_Name_ID<>$_POST[nSecurityGroup]

is users not included into security group
:)

in sql expressions not equal sign is <>.
What is need to know.That's all:)

[The_Warden]

<G>, I wish it was that simple. Normally that would work perfectly, but in my case it does not. The reason why it because a user can possibly be within multiple security groups. So saying that if I was not in the group being queried but in another I would still show up in the result set. I should have been more clear on this when I first posted the message.

Do you see what I mean?

[daisho]

LEFT OUTER JOIN.

Group by user.

This will give you a list of the access for everygroup for everyuser (NULL group if there is no access).

Check for all NULL. That will be a user with no rights.

This should give you a start.

daisho.

[The_Warden]

The left outer join clause returns the data required from the tblWebAccounts, but because of the condition needing to remove web account groups of a specific value NULLs are removed. Therefore over ridding the left outer join clause.

SELECT DISTINCT tblWebAccounts.Web_Account_ID, viewPeople.First_Name, viewPeople.Last_Name
FROM tblWebAccounts LEFT OUTER JOIN tblWebAccountGroup ON tblWebAccounts.Web_Account_ID=tblWebAccountGroup.Web_Account_ID, viewPeople
WHERE tblWebAccounts.People_ID=viewPeople.People_ID
AND tblWebAccountGroup.Web_Account_Group_Name_ID<>$_POST[nSecurityGroup]
ORDER BY viewPeople.First_Name, viewPeople.Last_Name

[BCMG_Scott]

Does MSSQL support sub-queries? MySQL doesn't yet, but will, Oracle does (and has for a while). To be more specific I mean:

select * from table
where user not in (select user from table
where user = 'blah')

and to combine the two queries:

select * from table
where user = 'blah'
union
select * from table
where user not in (select user from table
where user = 'blah'

Obviously this is an over-simplification of your query, but may work.

Scott Geiger