SQLTeam.com | Weblogs | Forums

NooB and stuck on simple Query


#1

This Query Below works fine, but returns me a bunch of records that need pruned out.

SELECT
START_RUN_DATE, JOB_CODE FROM PF_SCHEDULE WHERE FORM_CODE='".$row["FORM_CODE"]."' AND CC_CODE='$cc_code'

So what I need to do is compare record TO the table PF_JOBS

If no record in PF_JOBS exists where the fields JOB_CODE match, THEN I DO NOT WAT THAT RECORD IN MY QUERY.

Im not good with SQL but attempted this below and of course its not correct, I need help with the query syntax.. PLEASE J

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'WHERE'. (severity 15) in \ATEST-b.php on line 42

( DONT LAUGH, IM A BEGINNER ) :slight_smile:

SELECT START_RUN_DATE,
JOB_CODE FROM PF_SCHEDULE WHERE
FORM_CODE='".$row["FORM_CODE"]."' AND CC_CODE='$cc_code'
AND WHERE JOB_CODE IN PF_JOBS EXISTS";

Any ideas on how that query should be? Would be much appreciated. THANKS!


#2

Something like this:

SELECT START_RUN_DATE
     , JOB_CODE
FROM PF_SCHEDULE
WHERE FORM_CODE = '".$row["FORM_CODE"]."'
     AND CC_CODE = '$cc_code'
     AND
 JOB_CODE IN (
          SELECT JOB_CODE
          FROM PF_JOBS 
          );

#3

Thanks for the help! It was close.

So, it does not error, but it does not prune out the incomplete records. Hmmm. Maybe I am explaining it wrong.

So, table PF_JOBS is a table of live and current jobs.

All my other tables have records that may be completed, old work. So, I only want to pull records FROM PF_SCHEDULE that have a matching record in PF_JOBS. The related fields are JOB_CODE


#4

Which is precisely what my query does. It should be easy to verify. If you get even one row back where PF_SCHEDULE.JOB_CODE is not found in PJ_JOBS, SQL Server is broken.


#5

ERROR: Invalid object name 'PF_SCHEDULE'.
Error Code: 208

The cursor was not declared.

OK, must be an operator problem between your query and how I am using it. Not sure what this means but when I executed this in RazrSQL it gave me this. When I run it in my php script it works fine but doesn't prune out those records. Maybe I am missing a formatting character or such. What does this error say, cursor not declared?


#6

Dunno about "cursor". I don't use PHP. but the error message means that the table PF_SCHEDULE cannot be found