[RESOLVED] Join of somesort MySQL

Arc

Hey guys I have two tables, tblJobs and tblCustomers.

The tblJobs looks like this
JobID->Primary
CustomerID->Foriegn
UserID->Foriegn
RONumber
EstimateAmount
DateAdded
etc...

The tblCustomers looks like this
CustomerID->Primary
CustomerName
Street
City
DateAdded
etc...

Now, what I am trying to do is have a list of jobs that are selected between tow dates and the list should display

RONumber, CustomerName, EstimateAmount, JobID

My problem is getting the Customer Name. I have the CustomerID in the Jobs table, but I cant figure out how to get the CustomerName from the Customer table as a result in the query.

I tried doing something like this
$sql="SELECT * FROM tblCustomers AS a, tblJobs AS b WHERE UserID='$userID' AND b.DateAdded >= '$low' AND b.DateAdded <= '$high' AND a.CustomerID = b.CustomerID";

But that doesn't work right (returns no rows when it should). Also I am worried about the DateAdded fields messing with eachother and skewing the results.

Any Help is much appreciated! 🙂

Arc

Never mind, I decided to just use two different queries.

bpat1434

It can be done with one query:

SELECT c.CustomerName, j.*
FROM tblJobs AS j
INNER JOIN tblCustomers AS c
    USING(CustomerID)
WHERE j.UserID = '$userID'
    AND j.DateAdded >= '$low'
    AND j.DateAdded <= '$high'

That should do it.

Arc

That seems to work great, thanks a lot! I need to learn joins.

bpat1434

No problem..... a good SQL book would work well....

Arc

Thanks a lot for your help earlier.

I have come across an even more complex issue that is basicaly the same except I need to join another table to it. So 3 tables at work here.

I basicaly need to do the exact same thing as above except add the users table and get the WriterName based on the UserID.

The Users table has

UserID <--Primary
WriterName
etc...

So basicaly it would look like

$sql="SELECT c.CustomerName, u.WriterName,j.*
FROM tblJobs AS j
INNER JOIN tblCustomers AS c,tblUsers AS u
USING(c.CustomerID,u.UserID)
WHERE j.AccountID = '$accountID'
AND JobStatus = 'Active'";

That is totaly wrong of course. This is frying my brain, any help is appreciated!