[RESOLVED] Join 3 tables

[Arc]

Hey guys, I need to join three tables, but I have never done three and I can;t get it to work.

First let me show you the three tables

tblCustomers
CustomerID
AccountID
UserID
Name
etc..

tblUsers
UserID
AccountID
Username
etc..

tblJobs
JobID
AccountID
CustomerID
UserID
JobStatus
etc...

I need to get everything from the tblJobs, the Name from tblCustomers and Username from tblUsers all in one SQL statement if possible.

I have a SQL statement that joins 2 tables (tblJobs and tblCustomers) and works fine

$sql="SELECT c.Name, j.*
FROM tblJobs AS j
INNER JOIN tblCustomers AS c
    USING(CustomerID)
WHERE j.AccountID = '$accountID'
    AND j.DateAdded >= '$low'
    AND j.DateAdded <= '$high'
	ORDER BY j.$orderBy";

Now I want to add the third table tblUsers and get the Username from that.

Is this possible given the database design?

Thanks guys! Means a lot! 😕

[mjax]

You have a field UserID in both tblJobs and tblCustomers. From which table do you want to fetch the user's username?

[Arc]

UserID is in all three tables. I need to get the Username from the tblUsers.

[Arc]

I think I figured it out, but can someone eyeball my code and make sure it's right please? I don't want any unexpected results on down the road.

$sql="SELECT c.Name, j.*, u.WriterName
FROM tblJobs AS j
LEFT JOIN tblCustomers AS c ON c.CustomerID = j.CustomerID
LEFT JOIN tblUsers as u ON u.UserID = j.UserID
WHERE j.AccountID = '$accountID'
    AND j.DateAdded >= '$low'
    AND j.DateAdded <= '$high'
	ORDER BY j.$orderBy";

[ahundiak]

Looks good. As long as c.CustomerID and u.UserId are unique then you should be fine. If you ever have two users (or customers) with the same id then you will start getting multiple records for the same job.

[Arc]

Cool, thanks!