An IF statement in a where clause...

adavis

I have three tables that I'm comparing. I only want to flag records if any of certain fields do NOT match. The purpose is to flag these documents on 2 of the 3 tables so that data entry knows which records and fields need to be corrected. However, the customer only wants to loosely check this one particular field

I have one field that can be 'Y','N','', ' ','T','F' in each table. The easy part is that 'Y' matches to either 'Y' or 'T' and 'N' matches to either 'N' or 'F', but 'N' can also match to '' or ' '. When I read the data in from table 1, I can check this value and change it to either 'Y' or 'N', but when I do a select on table 2, I am having trouble figuring out what to do.

Here's my pseudo code with all the names are changed to protect the innocent or guilty as the case may be 😉 :

$sql_t1 = "
SELECT myField_t1
FROM table1
WHERE myLocation = 'HERE'
AND myDate	 = '0000-00-00'
AND myDocument = 'ABCDEFG'";
$myField_t1 = value($sql_t1,$db);     // where value is a function to get a single value

$sql_t2 = "
SELECT count(*)
FROM table2
WHERE myLocation = 'HERE'
AND myDate	 = '0000-00-00'
AND myDocument = 'ABCDEFG'
AND (fld1_t2 != '$fld1_t1'
 OR   fld2_t2 != '$fld2_t1'
...'";

and then I'm lost. When myField_t1 equals 'N', how to I NOT count this record if myField_t2 equals 'N','', or ' '?

tomhath

Quick and dirty would be something like this:

AND ( (fld1_t2 in ('T','Y') AND '$fld1_t1' in ('T','Y') )
    OR (fld1_t2 in ('N','F','',' ') AND '$fld1_t1' in ('N','F','',' '))
       )

Roger_Ramjet

Well yes, Tom, except he wants to count them when they are NOT both:

When myField_t1 equals 'N', how to I NOT count this record if myField_t2 equals 'N','', or ' '?

Now the best fix is to use a CASE in the SELECT clause to convert the column value to either 'N' or 'Y' accordingly. You would then be able to join the 2 tables and filter in the WHERE clause and/or GROUP easily

tomhath

Yea, I considered using CASE, either way should work if you get the other logic right. :o

Second attempt:

AND ( NOT ( (fld1_t2 in ('T','Y') AND '$fld1_t1' in ('T','Y') )
         OR (fld1_t2 in ('N','F','',' ') AND '$fld1_t1' in ('N','F','',' '))
            )
     )