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
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
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,
INNER JOIN
systypes s ON (s.SysTypeId = j.SysTypeId)
INNER JOIN
users u ON (u.UserId = j.CustId), -- I'm assuming that CustId makes reference to tbl user
INNER JOIN
site si ON (si.SiteId = j.SiteId)
WHERE j.CompletedBy ='0' AND j.AssignedTo='$mytechname'
I think this is what your looking for.
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
INNER JOIN systypes s ON j.SysTypeID=s.SysTypeID
INNER JOIN users u ON j.AssignedTo=u.UserID AND u.NameUser='$mytechname'
INNER JOIN site si ON j.SiteID=si.SiteID
WHERE j.CompletedBy ='0'
Assuming the name of column for user id in users table is UserId, try this query
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
AND j.AssignedTo=u.UserId