SQL - Join help 1

[EzLogic]

my brain is dead today...

what am i doing wrong?

Tables:
PortalSecurity
IID I AutoInc
SecName C(20)

PortalUsers
IID I AutoInc
UserName C(20)
etc..

PortalUserlevel
IID I AutoInc
fk_PortalSecurity
fk_PortalUsers

I want to get the Security levels, and display what the user has level checked.

SELECT PortalSecurity.SecName,;
PortalSecurity.scriptname,;
nvl(PortalUserLevel.Security,0) as Security ;
.f. as lChecked ;
FROM PortalSecurity ;
LEFT OUTER JOIN PortalUserLevel ;
ON PortalSecurity.iid = PortalUserLevel.fk_PortalSecurity ;
LEFT OUTER JOIN PortalUsers ;
ON Portaluserlevel.fk_PortalUsers = PortalUsers.IID ;
Where PortalUsers.IID = 1


Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[Mike Lewis]

what am i doing wrong?

Do you mean apart from the fact that you have two fields in your query that appear not to be in the tables (PortalUserLevel.Security and PortalSecurity.scriptname)?

Apart from that, the syntax looks OK, but I'm not sure exactly what you are aiming to achieve. What does "display what the user has level checked" mean?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[EzLogic]

Mike,

Basically, this is what i have and what i want to achieve.

PortalUsers (is a table that contains the user id etc..)
PortalSecurity (is a table that contains a Security IID, and a Script name)
PortalUserLevel (is a table that contains what User ID has what level)

what i want to do is, given any user ID i pass to my func in my class i want to get:

a list of all the security levels, and display which one the user has.. sometimes, it could be none.

example:
userID (TeknoSDS)

returns:

Account manager .t.
Sales Records .t.
Purchase Orders .f.
Inventory Module .f.


something like that.



Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[EzLogic]

sample data in my tables:

PortalSecurity
IID SecName ScriptName
1 Account Manager Account.fwx
2 Purchase Order PO.fwx
3 Inventory Control IC.fwx

PortalUsers
IID UserID Fullname etc..
1 TeknoSDS Ali Koumaiha

PortalUserLevel
IID fk_PortalUsers fk_PortalSecurity
1 1 3

That means, TeknoSDS has IC access only.

but, in the form (Grid)

I want to display All 3 PortalSecurity fields, and only IID=3 is checked (as TeknoSDS has that)

i hope this is more clearer

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[Olaf Doschke]

As you do OUTER joins you always have all combinations. That's okay. The security levels a user does not have will result in NULL values in the PortalUserLevel fields. So like you have done with nvl(PortalUserLevel.Security,0) you need an expression that returns .f. if PortalUserLevel.Security is null and .t. if not:

NOT isnull(PortalUserLevel.Security) as lChecked

 

[EzLogic]

Thanks Olaf,

however, if a user does not have anything checked yet (nothing in the PortalUserLevel)

my query returns no records.

i want it to return all the Level, without anything checked still.

but, no recs being returned.

i'd hate to do a scan/endscan and do a seek etc.. to get the stuff. i want to do it in a sql select.

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[EzLogic]

one way i was thinking of doing it:

query that gets me the user and what security IID level he has into a cursor curUserAccess

query that gets me a "copy" of the levels, with an additional field L, into a cursor readwrite curLevels


scan through curUserAccess
seek the Access IID i have
if found() in the curLevels
make the logical field = .t.
endif

make the grid record source to the curLevels cursor..



Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[EzLogic]

I got it to work, using a scan/end scan.. was hoping i can do it in One SQL select:

	FUNCTION GetPortalUserSecurity(tnIID)

		IF PCOUNT() = 0
			RETURN ""
		ENDIF 
		
		This.OpenTable("PortalUsers")
		This.OpenTable("PortalSecurity")
		This.OpenTable("PortalUserLevel")
		


		LOCAL lcCursor

		lcCursor = SYS(2015)

		
		SELECT PortalUsers.IID,;
			   NVL(PortalUserLevel.Security,0) as Security;
			   FROM PortalUsers ;
				LEFT OUTER JOIN PortalUserLevel ;
					ON PortalUsers.IID = PortalUserLevel.Portaluser ;
				Where PortalUsers.IID = tnIID ;
				INTO cursor curPortalUsersLevel


		SELECT SecName,;
		      .f. as CanAccess,;
		      IID ;
		      FROM PortalSecurity ;
		      INTO CURSOR (lcCursor) READWRITE 

		SELECT curPortalUsersLevel
		SCAN 
		   lnfk_Security = Security
		   SELECT (lcCursor)
		   LOCATE FOR IID = lnfk_Security
		   IF FOUND()
		   	REPLACE CanAcess WITH .t.
		   ENDIF 
		ENDSCAN 

		USE IN SELECT("curPortalUserslevel")

		This.CloseTable("curPortalUsersLevel")
		This.CloseTable("PortalUsers")
		This.CloseTable("PortalSecurity")
		This.CloseTable("PortalUserLevel")
				
		RETURN lcCursor
		
	ENDFUNC

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[Olaf Doschke]

You didn't got my explaination. Sample code speaks more than 1000 words:

Create Cursor PortalSecurity (IID I autoinc, SecName C(20), ScriptName C(20))
Insert into PortalSecurity (SecName, Scriptname) Values ("Account Manager", "Account.fwx")
Insert into PortalSecurity (SecName, Scriptname) Values ("Purchase Order", "PO.fwx")
Insert into PortalSecurity (SecName, Scriptname) Values ("Inventory Control", "IC.fwx")

Create Cursor PortalUsers (IID I autoinc, UserID C(20), Fullname C(30))
Insert Into PortalUsers (UserID, Fullname) Values ("TeknoSDS", "Ali Koumaiha")
Insert Into PortalUsers (UserID, Fullname) Values ("TekTips", "Olaf Doschke")

Create Cursor PortalUserLevel (IID I autoinc, fk_PortalUsers I,fk_PortalSecurity I)
Insert Into PortalUserLevel (fk_PortalUsers,fk_PortalSecurity) Values (1,3)

tnIID = 1

SELECT; 
    PortalSecurity.SecName,;
    PortalSecurity.scriptname,;
    nvl(PortalUserLevel.fk_PortalSecurity ,0) as Security,;
    Not IsNull(PortalUserLevel.fk_PortalSecurity ) as lChecked ;
    FROM PortalSecurity ;
    LEFT OUTER JOIN PortalUserLevel ;
       ON PortalSecurity.iid = PortalUserLevel.fk_PortalSecurity ;
       AND PortalUserLevel.fk_PortalUsers = tnIID

Bye, Olaf.

 

[EzLogic]

Wow.. Olaf.. i guess i didn't get your post initially..

i am gonna change my "long" code and use your suggestion.

worked very well!

Star for you!

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[Olaf Doschke]

Thanks, Ali!

As a personal preference I'd let NULL be NULL, but I can understand that in a logical expression like Security>xyz a NULL would hurt and so you simply prevent that alredy when selecting the data.

You could also reuse the NVL() expression:

SELECT;
PortalSecurity.SecName,;
PortalSecurity.scriptname,;
nvl(PortalUserLevel.fk_PortalSecurity ,0) as Security,;
nvl(PortalUserLevel.fk_PortalSecurity ,0)>0 as lChecked ;
FROM PortalSecurity ;
LEFT OUTER JOIN PortalUserLevel ;
ON PortalSecurity.iid = PortalUserLevel.fk_PortalSecurity ;
AND PortalUserLevel.fk_PortalUsers = tnIID

I don't know if vfp optimizes evaluating the same expression, but even if not, it looks a bit more elegant.

Bye, Olaf.