Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Ian_D]

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.

[jereboam]

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.

[Ironingboard]

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  :-/

[Ian_D]

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")%>&nbsp;<%=RSDisplay.Fields("FirstName")%>&nbsp;<%=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  

[jereboam]

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.

[Ironingboard]

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.

[Ian_D]

Hmm, Still stuck  :-/

Anyone got any other ideas?

[Martin_1962]

This makes me happy to use ISAM and do my own data collecting.

[jereboam]

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.

[Ian_D]

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:



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!)  

[albitz]

scuse my ignorance,wtf is sql ?  

[jereboam]

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…

[rad cap]

ive got a pen and paper if that helps  

[TheBoy]

scuse my ignorance,wtf is sql ?  

An (almost) universal way of accessing databases using a supposedly standard language.

Quite simple once you get hang of it

[tunnie]

scuse my ignorance,wtf is sql ?  

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.