I have a couple of tables and want to bring in data from both of them, with criteria on both. On one of them, I'm trying to bring in data if it exists, otherwise something else, plus additional criteria. Here is the Sample Data: Table 1 – Table Name ‘job’, with 2 fields (job_no & stat) job_no stat J001 C J002 R J003 R J004 C J005 R J006 R Table 2 – Table Name ‘jobop' with 4 fields (job_no, op_no, start_date & complete) job_no op_no start_date complete J001 40 01/07/2011 Y J001 550 04/07/2011 N J001 750 06/07/2011 N J002 550 12/07/2011 N J002 750 14/07/2011 N J003 40 02/07/2011 Y J003 550 05/07/2011 N J004 550 15/07/2011 N J004 750 18/07/2011 N J005 40 20/07/2011 N J005 550 22/07/2011 N J005 750 25/07/2011 N J006 550 20/07/2011 N J006 750 23/07/2011 N Here is my code so far, although I have hit a snag even with this: SELECT job.job_no AS JobNo, jobop.op_no AS OpNo, jobop.start_date AS StartDate FROM job RIGHT OUTER JOIN jobop ON job.job_no = jobop.job_no WHERE ((job.stat = N'R') AND (CASE WHEN jobop.op_no = '750' THEN jobop.op_no = '750' ELSE jobop.op_no = '550' END)) ORDER BY job_no The criteria I want to achieve is as follows: 1) Only show Jobs with a stat = ‘R’ 2) Where there is an op_no 750, show that op_no and the associated start_date. Where there isn’t a 750, show the op_no 550 and it’s associated start_date. 3) On Operations that have an op_no 40, do not show op_no 550 or 750 until op_no 40 is complete Number 1 above is working okay. Number 2 above doesn’t work and gives an error “Incorrect syntax near '='” Number 3 above I haven’t even tried to bring into the equation yet, due to number 2 not working yet. The results set should look like this: JobNo OpNo StartDate J002 750 14/07/2011 J003 550 05/07/2011 J006 750 23/07/2011 Can anybody help ?
Here is modification to your select script that is an option. It doesn't error out. SELECT job.job_no AS JobNo, jobop.op_no AS OpNo, jobop.start_date AS StartDate FROM job RIGHT OUTER JOIN jobop ON job.job_no = jobop.job_no WHERE ((job.stat = 'R') AND op_no in (select op_no from jobop where op_no = '750') or op_no in (select op_no from jobop where op_no = '550')) ORDER BY jobop.op_no desc
I tried the code you suggested, but was striggling to just bring in what I needed. Below is what I have used as a slightly clumsy looking workaround, but it works. I've done it like this, so I can test it after each level (temporary table). Temporary table 1 gives me the data to work with. Temporary table 2 gives me a unique job number (gets rid of duplicates) and sums the op_no and complete fields, so I can refer to these sums. Temporary table 3 looks at the summed data and works the permitations to give me a clean set of data which I then combine with fields from Temporary table 1 to give me my result set. I could probably have used one table less or done it another way, but this gives me the end goal which was: a) To show the data from op_no 750 and if that didn't exist to show the data from op_no 550 (for the same job_no). b) If the Job has an op_no 40 (only a smallish pecentage), then only show the op_no 750/550 data if op_no 40 is complete. Here is my code, hopefully this is correct, as my actual code brought data from 2 other tables and had a load of other criteria in it as well): DECLARE @JobInfo TABLE ( JobNo NVARCHAR(20) , OpNo INT , OpSD DATETIME , JobComp TINYINT , Op40Cmp INT , JobSt NCHAR(1)) INSERT INTO @JobInfo (JobNo, OpNo, OpSD, JobComp, Op40Cmp, JobSt) SELECT job.job_no AS JobNo , jobop.op_no AS OpNo , jobop.start_date AS OpSD , jobop.complete AS JobComp , CASE WHEN jobop.op_no = 40 AND jobop.complete = 0 THEN 50 ELSE 1 END AS Op40Cmp , job.stat AS JobSt FROM job RIGHT OUTER JOIN jobop ON job.job_no = jobop.job_no WHERE ((job.stat = N'R') AND (jobop.op_no IN ('40', '550', '750'))) ORDER BY job.job_no, jobop.op_no DECLARE @JobInfo1 TABLE ( JobNo1 NVARCHAR(20) , OpSum INT , Op40Sum INT) INSERT INTO @JobInfo1 (JobNo1, OpSum, Op40Sum) SELECT JobNo AS JobNo1, SUM(OpNo) AS OpSum, SUM(Op40Cmp) AS Op40Sum FROM @JobInfo GROUP BY JobNo DECLARE @JobInfo2 TABLE ( JobNo2 NVARCHAR(20) , OpNo2 INT , Op402 TINYINT) INSERT INTO @JobInfo2 (JobNo2, OpNo2, Op402) SELECT JobNo1 AS JobNo2 , CASE WHEN OpSum = '1300' THEN '750' WHEN OpSum = '1340' THEN '750' WHEN OpSum = '750' THEN '750' WHEN OpSum = '790' THEN '750' ELSE '550' END AS OpNo2 , Op40Sum AS Op402 FROM @JobInfo1 WHERE ((Op40Sum < 50) AND (OpSum = '550' OR OpSum = '590' OR OpSum = '750' OR OpSum = '790' OR OpSum = '1300' OR OpSum = '1340')) ORDER BY JobNo1 SELECT JobNo2 AS JobNum , OpNo2 AS OpNum , OpSD AS StartDate , CASE WHEN JobComp = 1 THEN 'Y' ELSE 'N' END AS Comp FROM @JobInfo2, @JobInfo WHERE ((JobNo = JobNo2) AND (OpNo = OpNo2)) ORDER BY JobNo2, OpNo2