SELECT...? SubQuery..? JOIN..? EXEC..?

[Magiaus]

	SELECT 
		Guid
	FROM
		ContactsPermissions
	WHERE ContactsPermissions.ContactGuid = @ContactGuid
	AND @ZoneName = (SELECT 
						SecurityZones.Name 
					FROM 
						SecurityZones 
					WHERE 
						SecurityZones.Guid = ContactsPermissions.SecurityZoneGuid)

^ Does not work.

SET @ZoneGuid = (SELECT Guid FROM SecurityZones WHERE SecurityZones.Name = @ZoneName)

SELECT Guid FROM ContactsPermissions 
WHERE ContactsPermission.ContactGuid = @ContactGuid
AND @ZoneGuid = ContactsPermissions.SecurtyZoneGuid

^ Will not save.

I'm trying to load a contact permission based on the contact guid and the name of the security zone. The name is unique and returns one result when you pull it by name. The Permissins pulls all permissions fine by ContactGuid.

ContactPermissions
---------------------------------------------------------------------------------
3 Guid uniqueidentifier 16 0
0 ContactGuid uniqueidentifier 16 0
0 SecurityZoneGuid uniqueidentifier 16 0

SecurityZones
---------------------------------------------------------------------------------
3 Guid uniqueidentifier 16 0
0 Name varchar 300 0
0 Description text 16 1

	SELECT     ContactsPermissions.Guid
	FROM         ContactsPermissions INNER JOIN
	                      SecurityZones ON ContactsPermissions.SecurityZoneGuid = SecurityZones.Guid
	WHERE  (ContactsPermissions.ContactGuid = @ContactGuid)
	AND    (SecurityZones.Name = @ZoneName)

^ Returns nothing even when passed valid param.

So basicly I know the contact, I know the Zone name and I need to get the Permission guid.

Thanks to anyone who can help.

[brucevde]

The last query should work, providing all your parameters values are correct. Is your SQL Server Case Sensitive?

The only thing I can suggest is to place square brackets around the Name field. Name is a reserved/keyword and may be causing problems.

AND (SecurityZones.[Name] = @ZoneName)

[Ecniv]

SELECT 
		Guid
	FROM
		ContactsPermissions
	WHERE ContactsPermissions.ContactGuid = @ContactGuid
	AND @ZoneName = (SELECT 
						SecurityZones.Name 
					FROM 
						SecurityZones 
					WHERE 
						SecurityZones.Guid = ContactsPermissions.SecurityZoneGuid)

To my untrained Sql Server eye this won't work because the second select might bring back more than one result. So I'd re-think your joins. Poss:

SELECT [Guid]
FROM [ContactsPermissions] Left join [SecurityZones] on
[SecurityZones].[Guid] = [ContactsPermissions].[SecurityZoneGuid]
WHERE [ContactsPermissions].[ContactGuid] = @ContactGuid
AND [SecurityZones].[name] = @ZoneName

I've gone mental with the square brackets, but it should work fine. As Bruce mentioned, its best to put field names in square brackets at least, especially if they happen to be functions.

The other sql statements I dunno. I don't even know if the one above will work right (the way you want) as people used where clauses to make joins... Confusing

Vince

[Magiaus]

	(
		@ContactGuid uniqueidentifier,
		@ZoneName varchar(300)
	)
AS	
	SELECT     [ContactsPermissions].[Guid]
	FROM         ContactsPermissions INNER JOIN
	                      SecurityZones ON [ContactsPermissions].[SecurityZoneGuid] = [SecurityZones].[Guid]
	WHERE  ([ContactsPermissions].[ContactGuid] = @ContactGuid)
	AND    ([SecurityZones].[Name] = @ZoneName)	RETURN

I tried that. No records. The Zones table is constrained to have a unique name. I also tried a distinct with the subquery variation it doesn't work either. This is really messing with me. Any ideas?

[Ecniv]

ok... lets take it back a step.

Leave the join in but take out hte zones part of the where clause and see if you get any records back.

If you do, then you need to check that the value put into @Zones is the same as the one you are filtering on. Maybe its filtering out the records (case sensitive?) even though you know its right.

If however you are not getting any records at all, I'd change inner join to left join and see whether that helps.

If this fails... then you/I/we'd better think up a new approach


Vince

[techgnome]

Orginal query:

	SELECT 
		Guid
	FROM
		ContactsPermissions
	WHERE ContactsPermissions.ContactGuid = @ContactGuid
	AND @ZoneName = (SELECT 
						SecurityZones.Name 
					FROM 
						SecurityZones 
					WHERE 
						SecurityZones.Guid = ContactsPermissions.SecurityZoneGuid)

"Updated" Query:

	SELECT 
		Guid
	FROM
		ContactsPermissions
	WHERE ContactsPermissions.ContactGuid = @ContactGuid
	AND @ZoneName IN (SELECT 
						SecurityZones.Name 
					FROM 
						SecurityZones 
					WHERE 
						SecurityZones.Guid = ContactsPermissions.SecurityZoneGuid)

Don't blink.... might miss it....

TG

[Magiaus]

I tried th IN version as well. I am getting recodrs if I remove the @ZoneName I also get records if I select using just the @ZoneName.

I am not sure what exactly what could be the problem.

This is my current attempt

	(
		@ContactGuid uniqueidentifier,
		@ZoneName varchar(300)
	)
AS	
	SELECT     [ContactsPermissions].[Guid]
	FROM         [ContactsPermissions] INNER JOIN
	                      [SecurityZones] ON [ContactsPermissions].[SecurityZoneGuid] = [SecurityZones].[Guid]
	WHERE  ([ContactsPermissions].[ContactGuid] = @ContactGuid)
	AND    ([SecurityZones].[Name] = @ZoneName)
	RETURN

I going to try using the left join in a bit as soon as I wrap up some other things I'm working on.

[brucevde]

This query should be simple.

As a test include the zonename in the selection list.

SELECT [ContactsPermissions].[Guid], ([SecurityZones].[Name]
FROM [ContactsPermissions]
INNER JOIN [SecurityZones] ON [ContactsPermissions].[SecurityZoneGuid] = [SecurityZones].[Guid]
WHERE ([ContactsPermissions].[ContactGuid] = @ContactGuid)

Run it and manually check if there is a match with your parameter.

Alternatively, if you have Enterprise Manager, create the query using the GUI and use the SQL statement it generates.

[Magiaus]

These are the results

{D241529A-ECE9-42A7-99CB-66699AE07DC Employee Production Agent                                                                                                                                                                                                                                        
{82EAAF2D-03DC-4867-90F6-2B7453C6B9A Employee Products Agent                                                                                                                                                                                                                                          
{F5C2C45A-392A-409A-90AF-645A7A573DB Employee Production Reviewer                                                                                                                                                                                                                                     
{2550287D-E025-4ECA-A5ED-681309AC26F Employee Administrator                                                                                                                                                                                                                                           
{DFF1AE94-4E2D-43F7-B340-8880DFAA226 Employee                                                                                                                                                                                                                                                         
{297F6446-E99C-49C5-9D20-EB57E1AA776 Employee Order Agent                                                                                                                                                                                                                                             
{B8FA1A0D-829D-4105-BC03-EC4935C8F80 Contact                                                                                                                                                                                                                                                          
{E967E1E1-8833-407F-B06D-FF3978F3988 Employee Manufacturer Admin                                                                                                                                                                                                                                      
{25745C59-CD37-4963-9271-C78F1D77DBE Employee Distributor Admin                                                                                                                                                                                                                                       
{458B472C-5181-423B-B808-62DCE27AA98 Employee Source Agent                                                                                                                                                                                                                                            
{E73EA957-5838-487B-A001-648CBDB4A0F Administrator                                                                                                                                                                                                                                                    
{DE027E1B-804C-4BFD-8B4E-21F39A8BBB7 Employee Source Reviewer

I ran this query with a value copied directly from those results.

(
		@ContactGuid uniqueidentifier,
		@ZoneName varchar(300)
	)
AS	
	SELECT     [ContactsPermissions].[Guid], [SecurityZones].[Name]
	FROM         [ContactsPermissions] INNER JOIN
	                      [SecurityZones] ON [ContactsPermissions].[SecurityZoneGuid] = [SecurityZones].[Guid]
	WHERE  ([ContactsPermissions].[ContactGuid] = @ContactGuid)
	AND ([SecurityZones].[Name] = @ZoneName)
	RETURN

These are results from a query on SecurityZones using only the Name

66042BA8-5957-4E90-900E-E68FF47ED10

so as you see it pulls a distinct record

using

	(
		@ZoneName varchar(300)
	)
AS
	SELECT Guid FROM SecurityZones 
	WHERE SecurityZones.Name = @ZoneName
	RETURN

I have tried to execute that procedure inside the one that isn't working but I have never used execute before.....

I really appreciate you guys helping me out. If you have any ideas please post them, any ideas. I'm about to go to pulling everything and just doing a loop or using a cursor and doing the loop in SQL Server.

[Magiaus]

I just tried using the IN with the sub query again and it returns 0 records

[brucevde]

It has to be a problem with the parameter. The query is correct.

What is the value of @ZoneName that you are using?
Is your database Case Sensitive?
Is the datatype of SecurityZones.Name a varchar like the parameter declaration?

Again, create the query using the GUI interface in EM? What is the SQL statement that it generates?

[Magiaus]

Running dbo."CP_SZ_Test" ( @ContactGuid = {6591B544-6A37-41EB-9D37-8CCFE7D05DC4}, @ZoneName = Administrator ).

Guid
------------------------------------
{E73EA957-5838-487B-A001-648CBDB4A0F
No more results.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."CP_SZ_Test".

ALTER PROCEDURE dbo.CP_SZ_Test
	(
		@ContactGuid uniqueidentifier,
		@ZoneName varchar(300)
	)
AS
	SELECT     ContactsPermissions.Guid
	FROM         ContactsPermissions INNER JOIN
	                      SecurityZones ON ContactsPermissions.SecurityZoneGuid = SecurityZones.Guid
	WHERE     (ContactsPermissions.ContactGuid = @ContactGuid) AND (SecurityZones.Name = @ZoneName)
	RETURN

[Magiaus]

That works. Can some one tell me why? It is identical and I didn't do anything except let the UI generate it.

[Magiaus]

Thanks for the help you guys.