A Query thats abit beyond me

[TEENFRONT]

Hi All,

 

Im doing something for work (unpaid lol) that basically takes 2 tables, links them, then seperates out the results into "chargable" and "non chargeable" ...

 

I need a query that will basically do this. Im going to type out what i think should happen.. 

 

OK 2 tables, incidents and jobs.

 

 

SELECT incidentNumber, code1 FROM incidents    ---- This gets ALL the incidents from the 1st table and returns all the $incidentNumber's aswell as $code1

 

SELECT job FROM jobs WHERE incidentNumber = $incidentNumber  ---- This is the 2nd table with a matching incidentNumber field that the same as the 1st table incidentNumber field .

 

AND THEN

 

SELECT job FROM jobs where code2 = $code1  ---- this selects all the jobs with a code2 from the 2nd table that matches code1 from the 1st table.

 

The end result is to only select incident numbers from the incidents table that have a matching code1 and code2 in the jobs table.

 

Where do i start? i need to join the queries and the tables somehow and pass code1 as a var to the last bit when matching code1 to code2.

 

 

[Adam]

Trying to understand your logic here. Could you provide some sample data and expected results to make things a little easier?

[jcbones]

Try this query.

 

SELECT 
   a.incidentNumber, b.job 
FROM 
   incidents AS a 
JOIN 
   jobs AS b 
ON 
   a.code1 = b.code2

 

This query selects incident numbers from incidents table, only if incidents code1 is equal to jobs code2.  It will also return the job (name?).