SQL PHP加入4个表

Question

ive had a look about and just cant get my head around this.

The following is what im trying to achieve:

JobID AssignedTo SchDate ContractNo VisitNo Equipment SiteName SiteContact SiteAddress SiteTel AddedBy
1     Mark       220314  1          12      3500      Tesco    Joe         21 spooner  123455   Admin

When i use the following Query it some how creates 2 results when i should only have one:

SELECT j.JobID, j.CompletedBy, u.NameUser, j.SchDate, j.CustID, j.ContractNo, j.VisitNo,
s.EqName, si.SiteName, si.SiteContact, si.SiteAddress, si.SiteTele  
FROM jobs j, systypes s, users u, site si 
WHERE j.CompletedBy ='0' AND j.AssignedTo='$mytechname' AND j.SysTypeID=s.SysTypeID 
AND j.SiteID=si.SiteID

Here are the tables in working with:

jobs

JobID completedBy AssignedTo SchDate CustID ContractNo VisitNo SysTypeID SiteID AddedBy
1     0           1          220314  1      1          12      1         1      2

site

SiteID CustID SiteContact SiteName SiteAddress SiteTel
1      1      Ann Jones   Tesco    21 spooner  123455
2      2      John Hulson tele     54 vic st   443212

systypes

SysTypeID EqCode EqName
1         350    3500
2         450    4500

users

UserID NameUser UserName UserPass
1      Mark     mmc      1234
2      Admin    Admin    1234

I think i'm on the right tracks, i just need some guidance with this one.

If you have any questions please ask, thanks in advance.

Results

Count: 2 JobID: 1 CompletedBy: 0 AssignedTo: Mark McGuinness SchDate: 22/03/2014 CustID: 1 ContractNo: 12 VisitNo: 4 Equipment Name: 3500 SiteName: Tesco SiteContact: Ann Jones SiteAddress: 21 Good Street Glasgow G14 4CA SiteTele: 1413216545
Count: 2 JobID: 1 CompletedBy: 0 AssignedTo: Admin SchDate: 22/03/2014 CustID: 1 ContractNo: 12 VisitNo: 4 Equipment Name: 3500 SiteName: Tesco SiteContact: Ann Jones SiteAddress: 21 Good Street Glasgow G14 4CA SiteTele: 1413216545

Answer 1

Here is the basic query that should get you going

SELECT j.JobID, j.CompletedBy, u.NameUser, j.SchDate, j.CustID, 
   j.ContractNo, j.VisitNo,s.EqName, si.SiteName, si.SiteContact,
   si.SiteAddress, si.SiteTele  
FROM jobs as j
INNER JOIN users as u 
  ON j.AssignedTo = u.NameUser
INNER JOIN sites as si
  ON j.CustId = si.CustId
INNER JOIN systypes as s 
  ON j.SysTypeID  = s.SysTypeID
WHERE j.CompletedBy ='0' 
  AND j.AssignedTo='$mytechname'

A few pointers on that solution :

INNER JOIN : this command will add a row in your rowset only if the current row has a matching row in the joined table. If you want to include data even without matching row, go with LEFT JOIN or RIGHT JOIN

Performance wise, you should use your UserId to make your join between the users and jobs table. By defaut to find the match MySQL fetch a limited number of bit data to find a matching key, using a string, you can fit less elements in each search iterations. When not using primary keys to make a join, make sure to create an index on your joined column

Finally, security wise, you should rely on parametrized queries instead of using your variables in your query string