Skip to main content
E
emmim44
Inspiring
June 3, 2009
Question

Complex SQL 05 Query

  • June 3, 2009
  • 1 reply
  • 1705 views

I need big help with this logic...I am not familiar with looping in SQL...functions..
I have six tables..

CCFUser TB: {First_name,Last_Name,EID, Work_Status,CSGID,CCFRole,ModifyDate, LoginDate,CCFLevelID }
Snapshot TB: {SnapshotID,pDeptID....}
Sheet Tb : { SnapshotID,EID....}
Department :{ Department,DeptID }
User_TBL : {First_Name,Last_Name,User_ID,SecurityLevel_ID,LastLogIn,DeptID}
DeptUser: {User_ID,DeptID}
------------
The requirements:
Display all the fields on CCFUser and Department.Department
Loop thru ccfuser (make sure there are on sheet , snaphot and department table)
If CCFUser.CCFLevelID EQ 4
Then go to user_tbl table using first_name and last_name pick the lastloginDate if Department.DeptID is accesstible by the current user on DeptUser.
if yes return user_tbl.lastloginDate
--------
Table relationships:
CFFuser.EID = Sheet.EID
CCFUser.First_Name = User_Tbl.first_name
CCFUser.Last_Name = User_Tbl.Last_name
CCFUser.CCFLevelID  = User_Tbl.SecurityLevel_ID
Snapshot.SnapshotID = Sheet.SnapshotID
Department.DeptID  = Snaphot.pDeptID
User_TBL.user_id =DeptUser.User_ID
Department.DeptID =DeptUser.DeptID

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
June 3, 2009

I'm not sure what you would want to use looping logic?

Your requirements seem more likely solvable with some joins and maybe a max an or a min function.

E
emmim44Author
Inspiring
June 3, 2009

so if that is the case, please help me. I stucked on the first query...And then delete it...

ilssac
ilssac
Inspiring
June 3, 2009

The biggest help I can probably give is:

Sams Teach Yourself SQL in 10 Minutes - Third Edition by Ben Forta

OR

Database Design for Mere Mortals by Michael J. Hernandez

http://books.google.com/books?id=dkxsjXNayHQC&dq=database+design+for+mere+mortals&printsec=frontcover&source=bn&hl=en&ei=iOMmSoj0OJP6tAPU5oCOBg&sa=X&oi=book_result&ct=result&resnum=7

OR

SQL Tutorial at w3schools.com

http://www.w3schools.com/sql/default.asp

OR

Innumerable other books, web site and other resources.

This is about as fundamental as one can get when working with databases.

But to get you started, some of your SQL may look a bit like this:

SELECT CCFUser.*, Department.department
FROM user_tbl INNER JOIN
     deptuser ON (user_tbl.user_id = deptuser.user_id) INNER JOIN
     department ON (deptuser.deptid = department.deptid) INNER JOIN
     snapshot ON (department.deptid = snapshot.pdeptid) INNER JOIN
     sheet ON (snapshot.snapshotid = sheet.snapshotid) ...
WHERE
     CCFUser.CCFLevelID = 4

That is all I can work myself up to trying to understand all the relationships in your database, but hopefully that gives you a basic idea of the syntax.

With something this complex it is much easier to start with the most basic table.  Write the SQL to return the proper records from that table, probably CCFUser in your case here.  Then once you have that done, add the next table, make sure you are still getting the correct records.  Repeat until you have all the table joined together.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices