Home » RDBMS Server » Server Administration » PLEASE HELP!!! JOIN STRING
PLEASE HELP!!! JOIN STRING [message #371368] Sat, 07 October 2000 22:43 Go to next message
ann
Messages: 24
Registered: August 2000
Junior Member
Hi and good day,

I have this sql string, it's basicly pulling data from two tables, one is the category and the second one is the pages within each category. This string is for the treeview.

First I don't know what the darn thing does, so someone can explain me (it takes the fields .... and join them with ....) I'd be very happy.

The second thing is the left join, again your help would mean a lot for me.

What I need to do is to add a new User ID field in the pages table so the entire tree could be personal. My aim is to pull each User pages by adding some sort of WHERE U_ID=" ".

Please help me since I'm stuck on this

Thanx in advance,

Ann

strsql = "SELECT tblCategory.level, tblCategory.ID, tblCategory.Sort AS CatSort, tblPages.sort AS LinkSort, tblCategory.Name AS CatName, tblCategory.HREF AS CatURL, tblPages.Name AS LinkName, tblPages.href AS LinkURL FROM tblCategory LEFT JOIN tblPages ON tblCategory.ID = tblPages.ID ORDER BY tblCategory.Sort, tblPages.sort"
Re: PLEASE HELP!!! JOIN STRING [message #371369 is a reply to message #371368] Sun, 08 October 2000 09:30 Go to previous message
Prem
Messages: 79
Registered: August 1998
Member
Ann,

Assumptions:
You r new to SQL.
The SQL you pasted was generated by some wizard, maybe the MS Access query builder.
You are trying to understand it or you want an Oracle equavalent of the command.

First of all, grab a good book on SQL as whatever we explain here might just be a a quick fix. A strong knowledge of SQL is as important as knowing french in europe :) So here goes the quick fix

I guess you have used the MS Access query builder. All its doing here is to define the fields that are to be retrieved from two tables, Categories and Pages which have 1 to many relationship. i.e. a record in category table has one or more related records in the pages table. This said, then what are all those wierd brackets?! Lets dissect your query. Those brackets are not required if the field names dont have spaces. i.e. if you had a field called "Employee Name" instead of EmpName, you need a bracket for the Employee Name to tell the sql parser that its just one field. So all those extra [[]] in your query can be removed since all the field names are one word.
Every field is prefixed with the table name. This is required especially when there are fields in the two tables having the same name. When you say "Select an employee number from employee table and Loan table", how will sql know whether the employee number is from loan or employee table. So you are explicitly specifying the table name. Now you can see that every field is either prefixed by tblCategory or tblPages. So the first set just chooses the fields.
Now the From clause tells you what tables to involve in the join. As you might have guessed, its the tblCategory and tblPages, just a simple join(ing). Now how does your database know the relationship between the two tables. Whether they are related to each other using Name or Sort or level...? So you say that the tblCategory.ID field of tblCategory is related to the tblPages.ID of tblPages so that for every record selected from category table, it searches for records in tblPages where both the ID values are same. The last part 'ORDER BY' tells in what order the records which are selected should be sorted. Again, the tablename is prefixed before the fields to avoid ambiguity.

Now, the LEFT join part. Let me give u an example. We have the famous Department and Employee table. Department table has DeptId, DeptName and Location. Employee table has DeptId, EmployeeID, EmployeeName and Salary.

For every record in the Department table, there are one or more records in the employee table and the two tables are related by the DeptId field which is common in both. Now if you use the INNER JOIN which is the default, it will retrieve all records in both the table which has the same DeptID. Now, what if there is a department in dept table, but no employees for that dept in employee. When you use INNER JOIN, this department record will not be selected by the query.
Another situation, though this should not have happened in the first place. There is an employee record for DeptId = 10, but there is no such department in department table. Now when INNER join is used, this employee record will not be selected b'cos there is no corresponding record in the department table. To avoid this omission, you use the LEFT JOIN/RIGHT JOIN.

LEFT JOIN = This is to tell the database that even if you dont find a matching record in the second table (tblPages in your case), retrieve all the records in the first table. So if a category does not have any pages, still that category will be selected.

RIGHT JOIN = Its just the opposite of LEFT JOIN. It says , select all the records from tblPages table even if the category in tblPages don't exist in tblCategory (again, this shouldn't have happened in the first place- B.Codd will be rotating in his grave).

Lastly, the extra names like 'AS LinkSort..' is an alias to give a different name instead of the field name. It becomes more important when you use self-joins.

If you are executing the same on oracle, the sql can be simplified as


strsql = "SELECT tblCategory.level, tblCategory.ID, tblCategory.Sort CatSort, tblPages.Sort LinkSort, tblCategory.Name CatName, tblCategory.HREF CatURL, tblPages.Name LinkName, tblPages.HREF LinkURL FROM tblCategory, tblPages WHERE tblCategory.Id = tblPages.Id (+) ORDER BY tblCategory.Sort, tblPages.Sort"

This is the oracle equivalent of your query. Instead of specifying LEFT Join, (+) does that. If you are using this in MS Access, it wont run.

Now, the last part of the question. You want to add the U_ID to limit only to a certain user. Lets assume the user_id is stored in numUserID variable.

If you want the sql in the same style as it is now, here it is

strsql = "SELECT tblCategory.level, tblCategory.ID, tblCategory.Sort AS CatSort, tblPages.sort AS LinkSort, tblCategory.Name AS CatName, tblCategory.HREF AS CatURL, tblPages.Name AS LinkName, tblPages.href AS LinkURL FROM tblCategory LEFT JOIN tblPages ON tblCategory.ID = tblPages.ID WHERE tblPages.U_ID = " & cstr(numUserID) & " ORDER BY tblCategory.Sort, tblPages.sort"

If you want the sql that can run on oracle database, here it is

strsql = "SELECT tblCategory.level, tblCategory.ID, tblCategory.Sort CatSort, tblPages.Sort LinkSort, tblCategory.Name CatName, tblCategory.HREF CatURL, tblPages.Name LinkName, tblPages.HREF LinkURL FROM tblCategory, tblPages WHERE tblCategory.Id = tblPages.Id (+) AND tblPages.U_ID = " & cstr(numUserID) & " ORDER BY tblCategory.Sort, tblPages.Sort"

hth

Prem :)
Previous Topic: date + date not allowed.
Next Topic: top n records
Goto Forum:
  


Current Time: Fri Mar 29 05:37:44 CDT 2024