CODEWITHSUNDEEP
mysql
Acoustic Jack
Acoustic Jack

Reputation: 117

SQL JOINS not working

I have the following tables

1. tblJobs

JobID       int primary key

JobTypeID   int

JobClientID int

JobStaffID  int

....

2. tblContacts

ContactID   int primary key

ContactName varchar

....

3. tblJobTypes

TypeID      int primary key

TypeName    varchar

I can SELECT data from these table with this SQL...

SELECT tblContacts.ContactName, tblContacts.ContactID, 
tblJobs.JobID, tblJobs.JobTypeID, tblJobs.JobClientID, tblJobs.JobStaffID, 
tblJobTypes.* FROM (tblJobs LEFT JOIN tblJobTypes ON tblJobs.JobTypeID = tblJobTypes.TypeID)  LEFT JOIN tblContacts ON tblJobs.JobClientID = tblContacts.ContactID;

An example row from the SQL...

| ContactName| ContactID | JobID | JobTypeID | JobClientID | JobStaffID |TypeID | TypeName |

| Mr Contact |       290 |   341 |         3 |         290 |        202 |     3 | Enquiry  |

As you can see this SQL joins the tblJobs.JobClientID to tblContacts.ContactID(290). This is how I get tblContacts.JobStaffID (202).

How can I modify the SQL to get tblContacts.ContactName?

I've tried joining tables twice but with no success.

Upvotes: 0

Views: 78

Answers (1)

jpw
jpw

Reputation: 44881

Assuming that JobStaffID is a foreign key relating to tblContacts.ContactID you're correct in assuming that you need to join the tblContacts table twice - but you have to give it different aliases in each join like this:

SELECT 
    c1.ContactName as ClientName, c1.ContactID as ClientID, 
    c2.ContactName as StaffName,  c2.ContactID as StaffID, 
    j.JobID, j.JobTypeID, j.JobClientID, j.JobStaffID, 
    jt.TypeID, jt.TypeName     
FROM tblJobs j
LEFT JOIN tblJobTypes jt ON j.JobTypeID = jt.TypeID
LEFT JOIN tblContacts c1 ON j.JobClientID = c1.ContactID
LEFT JOIN tblContacts c2 ON j.JobStaffID  = c2.ContactID;

And while at it you might want to use aliases for all tables to reduce the query text.

Upvotes: 2

Related Questions