Omega Owners Forum
Chat Area => General Discussion Area => Topic started by: Ian_D on 24 April 2008, 14:45:26
-
Im new to SQL...
Basicly, Ive got 3 tables... Job, Client and Staff
The Job table will contain Job details, the clientsID and the SaffID
I can Join two tables using this:
SELECT * FROM Job INNER JOIN Client ON Job.ClientID = Client.ClientID
The question is how do I do that AND the StaffID at the same time?
thanks in advance.
-
SELECT * FROM Job
INNER JOIN Client ON Job.ClientID = Client.ClientID
INNER JOIN Staff ON Job.staffID = Staff.StaffID
assuming, of course, that the Job table actually has foreign keys to both the other tables.
-
it depends on what info your looking for from the DB, if your looking for all records from the job table where job.clientid = client.client id AND job.staffid = staff.staffid.
your could use
SELECT * FROM job
WHERE job.clientID = client.clientID
AND job.staffID = staff.staffID.
Why do you need to join tables, since all the Oracle SQL I've done its only been selecting data using certain criteria. Its maybe just the work I've done that doesn't use them :-/
-
Basicly for my college project I have to build a job sheet DB system.
To stop repeating data, I am using StaffID's and ClientID's
I need to display a list of jobs, however needs to say the clients name, not their ID, and the same with the Staff.
To make things a bit more tricky, its all done through ASP (which im also new too!)
Upto now ive got this:
<table border="0" align="center" rules="rows"><b>
<tr align="left">
<th width="50"><font color="#ff0000">Job</th>
<th width="150"><font color="#ff0000">Client</th>
<th width="150"><font color="#ff0000">Caller</th>
<th width="150"><font color="#ff0000">TakenBy</th>
<th width="100"><font color="#ff0000">DateCreated</th>
<th width="100"><font color="#ff0000">TimeCreated</th>
<th width="100"><font color="#ff0000">Status</th>
<th width="400"><font color="#ff0000">Description</th>
</tr></b></font>
<%
Set RSDisplay=Server.CreateObject("ADODB.Recordset")
SQL="SELECT * FROM Job INNER JOIN Client ON Job.ClientID = Client.ClientID"
RSDisplay.Open SQL,db
Do While Not RSDisplay.EOF %>
<tr valign="top">
<td><%=RSDisplay.Fields("JobNo")%></td>
<td><%=RSDisplay.Fields("Title")%> <%=RSDisplay.Fields("FirstName")%> <%=RSDisplay.Fields("LastName")%></td>
<td><%=RSDisplay.Fields("CallerID")%></td>
<td><%=RSDisplay.Fields("TakenBy")%></td>
<td><%=RSDisplay.Fields("DateCreated")%></td>
<td><%=RSDisplay.Fields("TimeCreated")%></td>
<td><%=RSDisplay.Fields("StatusID")%></td>
<td><%=RSDisplay.Fields("Description")%></td>
</tr>
<% RSDisplay.MoveNext
Loop
db.close
%>
As it stands, It will now display the clients name (not its ID) and I need to get the staff one to do the same.
Any ideas as im stuck :-[
-
The JOIN keyword is ANSI standard. It will work for any SQL database (in theory).
Oracle now recommend that you use this format in preference to the older table list in the WHERE clause.
-
The JOIN keyword is ANSI standard. It will work for any SQL database (in theory).
Oracle now recommend that you use this format in preference to the older table list in the WHERE clause.
Why am I not surprised, place I used to work were real old hat, they used latest version of oracle but my god, using notepad for developing C was taking the biscuit. Not surprised that they don't recommend WHERE clauses, in my work all they used was WHERE clauses, thank god I was made redundant and didn't learn anymore old methods.
-
Hmm, Still stuck :-/
Anyone got any other ideas?
-
This makes me happy to use ISAM and do my own data collecting.
-
Martin - The SQL isn't the problem - it's the embedding it in all this new-fangled object stuff that creates the confusion.
Ian - Right, then. Can't sort this without an ERD or an Object Model, 'cos I don't think I understand your problem. It looks like you need to find a value to put into your screen display TakenBy field. I doubt if the database field is called that - it's probably something like "Name", because that's what you'd expect to find in a Staff table.
You can PM me with the details and I'll try and sort it out, but ASP isn't my area - I just do databases.
-
Martin - The SQL isn't the problem - it's the embedding it in all this new-fangled object stuff that creates the confusion.
Ian - Right, then. Can't sort this without an ERD or an Object Model, 'cos I don't think I understand your problem. It looks like you need to find a value to put into your screen display TakenBy field. I doubt if the database field is called that - it's probably something like "Name", because that's what you'd expect to find in a Staff table.
You can PM me with the details and I'll try and sort it out, but ASP isn't my area - I just do databases.
Thanks, I really appreciate this help guys!
Here is the erd for the system:
(http://www.ijdonline.co.uk/oof/erd.jpg)
Its a job sheet system for an IT company where I work, which I’m doing as a project for my Professional Project Module at college.
A Quick explanation... The 'TakenBy' is the staff member who booked the job onto the system and the 'Owner' is the member of staff that is responsible for that job. (which by default, it will be the same as 'TakenBy'
I would appreciate any feedback on this system (Good / Bad, or even a completely different ERD if need be!) :y
-
scuse my ignorance,wtf is sql ? ;D
-
SQL - Structured Query Language. It's a non-procedural programming language for accessing relational databases. Any the wiser?
Ian - Try the following:
Select j.JobNo, c.Title, c.FirstName, c.LastName,
j.CallerID, s.StaffID, j.DateCreated, j.TimeCreated,
j.StatusID, j.Description
from Job j INNER JOIN Client c ON j.ClientID = c.ClientID
INNER JOIN Staff s ON j.TakenBy = s.StaffID
What is CallerID? Seems to be a foreign key, but not to any of the tables shown. And I presume that the StaffID equates to the Staff Number and that is what you want to display. Otherwise the ERD looks fine to me.
Hope it works, but I can’t guarantee the code – don’t do too much of this stuff these days.
I only joined today, and that was just to get my car fixed…
-
ive got a pen and paper if that helps ;D
-
scuse my ignorance,wtf is sql ? ;D
An (almost) universal way of accessing databases using a supposedly standard language.
Quite simple once you get hang of it :)
-
scuse my ignorance,wtf is sql ? ;D
An (almost) universal way of accessing databases using a supposedly standard language.
Quite simple once you get hang of it :)
Orginally SQL was a Microsoft / Sybase partnership, but they decided to go their seperate ways.
Some code is specific to certain databses.
-
scuse my ignorance,wtf is sql ? ;D
An (almost) universal way of accessing databases using a supposedly standard language.
Quite simple once you get hang of it :)
Orginally SQL was a Microsoft / Sybase partnership, but they decided to go their seperate ways.
Some code is specific to certain databses.
SQL was invented in the 1970s pre Microsoft
-
scuse my ignorance,wtf is sql ? ;D
Did you not know ::) ::) ::)
I didn't either, :-[ :-[ :-[ so pleased I was not alone. ;D ;D ;D
-
whats a computer?
-
SQL - Structured Query Language. It's a non-procedural programming language for accessing relational databases. Any the wiser?
Ian - Try the following:
Select j.JobNo, c.Title, c.FirstName, c.LastName,
j.CallerID, s.StaffID, j.DateCreated, j.TimeCreated,
j.StatusID, j.Description
from Job j INNER JOIN Client c ON j.ClientID = c.ClientID
INNER JOIN Staff s ON j.TakenBy = s.StaffID
What is CallerID? Seems to be a foreign key, but not to any of the tables shown. And I presume that the StaffID equates to the Staff Number and that is what you want to display. Otherwise the ERD looks fine to me.
Hope it works, but I can’t guarantee the code – don’t do too much of this stuff these days.
I only joined today, and that was just to get my car fixed…
no,sorry,youve lost me ;D ;D
-
whats a computer?
It's a box with bits in it, with a keyboard and mouse connected to it that you bash and bang to help you feel better about your own inadequacy to use it properly.
-
SQL - Structured Query Language. It's a non-procedural programming language for accessing relational databases. Any the wiser?
Ian - Try the following:
Select j.JobNo, c.Title, c.FirstName, c.LastName,
j.CallerID, s.StaffID, j.DateCreated, j.TimeCreated,
j.StatusID, j.Description
from Job j INNER JOIN Client c ON j.ClientID = c.ClientID
INNER JOIN Staff s ON j.TakenBy = s.StaffID
What is CallerID? Seems to be a foreign key, but not to any of the tables shown. And I presume that the StaffID equates to the Staff Number and that is what you want to display. Otherwise the ERD looks fine to me.
Hope it works, but I can’t guarantee the code – don’t do too much of this stuff these days.
I only joined today, and that was just to get my car fixed…
I will give that a go after my lunch! :y
Sorry, I forgot all about the CallerID, yeh there is another table, but i removed it from the ERD as it was a bit misleading!
Cheers :)
-
Ive tried all the above, and still not working :'(
Im totaly lost now :-[
Will have another play with it now and see what happens....
-
[size=18]YESSSSSSSSSSSSSS![/size]
I 'Think' ive sorted it..
Stupid MS Access connection requires brackets!
Ive now been at this for bloody ages! Started Yesterday at lunch time, and didn’t give up till 3:45am! (15+ hrs of my life wasted! ;D)
Anyway, this morning I continued... and 2 hrs later I've come up with this:
<table border="0" align="center" rules="rows"><b>
<tr align="left">
<th width="50"><font color="#ff0000">Job</th>
<th width="150"><font color="#ff0000">Client</th>
<th width="150"><font color="#ff0000">Caller</th>
<th width="150"><font color="#ff0000">TakenBy</th>
<th width="100"><font color="#ff0000">DateCreated</th>
<th width="100"><font color="#ff0000">TimeCreated</th>
<th width="100"><font color="#ff0000">Status</th>
<th width="400"><font color="#ff0000">Description</th>
</tr></b></font>
<%
Set RSDisplay=Server.CreateObject("ADODB.Recordset")
SQL="Select * FROM (((Job INNER JOIN Client ON Job.ClientID = Client.ClientID)" & _
" INNER JOIN Staff On Job.TakenBy = Staff.StaffID) INNER JOIN Status ON Job.StatusID = Status.StatusID) " & _
"LEFT OUTER JOIN Caller On Job.CallerID = Caller.CallerID"
RSDisplay.Open SQL,db
Do While Not RSDisplay.EOF %>
<tr valign="top">
<td><%=RSDisplay.Fields("JobNo")%></td>
<td><%=RSDisplay.Fields("ClientTitle")%> <%=RSDisplay.Fields("ClientFirstName")%> <%=RSDisplay.Fields("ClientLastName")%></td>
<td>
<%
If RSDisplay.Fields("CallerID") <> 0 then
Response.Write(RSDisplay.Fields("CallerFirstName"))
Else
Response.Write("<==")
End If
%></td>
<td><%=RSDisplay.Fields("SFirstName")%> <%=RSDisplay.Fields("SLastName")%></td>
<td><%=RSDisplay.Fields("DateCreated")%></td>
<td><%=RSDisplay.Fields("TimeCreated")%></td>
<td><%=RSDisplay.Fields("Status")%></td>
<td><%=RSDisplay.Fields("Description")%></td>
</tr>
<% RSDisplay.MoveNext
Loop
RSDisplay.Close
db.close
%>
</table>
fingers crossed now that it keeps working! :y
-
Congratulations!
I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago. It isn't suitable for use in large applications.
That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context. I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't). I may ask one of the MS specialists at work - they do this sort of stuff.
Anyway, I'm very glad you got it working.
-
Congratulations!
I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago. It isn't suitable for use in large applications.
That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context. I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't). I may ask one of the MS specialists at work - they do this sort of stuff.
Anyway, I'm very glad you got it working.
Im just a bit worried how its going to perform when theres a few 100 records in it :o :-/
-
Congratulations!
I've never seen anything like that, but then I have religiously avoided trying to use MS Access as a real database since I used it for small internal admin system 10 years ago. It isn't suitable for use in large applications.
That SQL isn't ANSI standard, by the way, and I don't think it would work in any other context. I'm really surprised that you can actually use brackets in a FROM clause (other than in a sub-select, which this isn't). I may ask one of the MS specialists at work - they do this sort of stuff.
Anyway, I'm very glad you got it working.
Im just a bit worried how its going to perform when theres a few 100 records in it :o :-/
Oh Dear...
My server at home has now died on me :'(
Balls! Im at work atm, so cant just reboot it / check internet connection!
Ah well, guess I will have to wait till I get back home.
My Pic has dissaperad too <=== :'(
-
I don't think you'll have too much trouble with hundreds of records, or even thousands, but if you need to manage tens of thousands, then Access is probably not the best choice.
Depends what it's for, and how many users you've got, I suppose.
-
I work with a relational database where users have access to well over 500,000 records at any one time. This is using Sybase as the engine. We run SQL's on them databases all the time, and sure, there is a bit of lag, but they work fine.
-
I don't think you'll have too much trouble with hundreds of records, or even thousands, but if you need to manage tens of thousands, then Access is probably not the best choice.
Depends what it's for, and how many users you've got, I suppose.
I did manage to get it to work using 3 or 4 WHERE statments. BUT... I worked out that it was filtering through the data, looping round, and if I had 99 Jobs on the system, it would have to search 96,059,601 records. Unless Ive got the wrong end of the stick somewhere? :-X
-
Good grief!
I wouldn't know where to begin tuning that one. Given that you are actually using dynamic SQL rather than talking straight to the database, I haven't got any idea where to start looking for the execution plan. Does Access have an optimiser? Try reading the help files on execution plans and optimisation.
Did I mention I've got 700,000 lines of Java to go through to find the embedded SQL and optimise that? :'(
I don't even speak Java...