Not In?

[drgonzo1967]

Hello all. Just need a little help from someone who knows more sql then I (there must be millions).

Quick background:
Domain = DOMAIN.COM
OU1 = WORKSTATIONS (under domain.com where wkstation objects are created)
OU2 = TEST (located under WORKSTATIONS ou where objects are moved)

Goal: To display only objects in WORKSTATIONS and NOT in TEST.

Query1:
SELECT DISTINCT sys.name0, wks.ResourceID, wks.System_OU_Name0
FROM v_RA_System_SystemOUName wks join v_R_System sys
on wks.resourceid = sys.resourceid
WHERE System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS/TEST'

--Then created a View named 'system_ou_name0_test'

Query2:
SELECT sys.name0, wks.ResourceID, wks.System_OU_Name0
FROM v_RA_System_SystemOUName wks join v_R_System sys
on wks.resourceid = sys.resourceid
WHERE System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS'

--Then created a View named 'system_ou_name0_wks'

Query3:
select distinct *
from system_ou_name0_wks
where ?? not in
(select *
from system_ou_name0_test)

--The '??' is where I am unsure, QA is saying that is where my problem is. What do I need to put here? Will this work at all or am I doing this completely wrong?

Any help here would be greatly appreciated! tia.

-mike

 

[bborissov]

select distinct *
from system_ou_name0_wks
where  System_OU_Name0 not in
(select  System_OU_Name0
from system_ou_name0_test)

Borislav Borissov

 

[drgonzo1967]

bborissov, thanks for the quick reply. Now the query completes sucessfully, but the objects in 'TEST' are still displayed. Is there a way that I could only display what is in 'WORKSTATIONS' w/o displaying what is in 'TEST' as well?

ex:
Objects in \WORKSTATIONS = PC1, PC2
Objects in \WORKSTATIONS\TEST = PC3

I would like the query results to only list PC1 and PC2, currently all three are there. Let me know if I can provide any additional information.

Thanks again!
-mike

 

[drgonzo1967]

Actually bborissov, 'NOT IN' may be incorrect. Because Ouery1 and Query2 both yeild PC3. Any ideas?

 

[Joulius]

Try this:

SELECT sys.name0,
       wks.ResourceID,
       wks.System_OU_Name0
FROM v_RA_System_SystemOUName wks
INNER JOIN v_R_System sys ON wks.ResourceID = sys.ResourceID
LEFT JOIN
(
       SELECT ResourceID
       FROM v_RA_System_SystemOUName
       WHERE System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS/TEST'
) wks_ex ON wks.ResourceID = wks_ex.ResourceID
WHERE wks.System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS'
      AND wks_ex.ResourceID IS NULL

 

[drgonzo1967]

Joulius, you rock. I notice you are not even using the views I created. Can you tell me how the INNER JOIN, LEFT JOIN, and wks_ex work exactly?

And Thanks Again!!
-mike

 

[Joulius]

Goal: To display only objects in WORKSTATIONS and NOT in TEST.

Table wks_ex contains all records from table v_RA_System_SystemOUName that match the condition System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS/TEST'.
To extract all records from table v_RA_System_SystemOUName that match the condition System_OU_Name0 = 'DOMAIN.COM/WORKSTATIONS' and are not in table wks_ex (assuming that ResourceID is the key for each object in v_RA_System_SystemOUName table) you must join the two tables on a N-to-N relation (LEFT OUTER JOIN) so you can find out the records that do not match.
Applying a WHERE [right_table].[key] IS NULL condition you will get the records from left table (in our case, the wks table) that can not be found in the right table (wks_ex table).

I have found to be a very good resource for learning SQL, the SQL Server Books Online tutorial that comes with the MS SQL Server installation kit.

I hope this helps!

 

[drgonzo1967]

Thank you Very Much for your help!!!

-mike