Selecting a matching field from table

[InteractiveMonke]

Hi

I have a table with multiple date fields and I want to select the matching field and one other field if one of those 10 date fields matches the criteria I have, (code below)

eg, P.date_invest matches my $date value, so I select P.date_invest and other field that I know is related to date_invest, otherwise look for a next match.

Basically what i is an IF() clause in the query somewhere, but I can't find anything like that.

Does any one know if its possible ?

SELECT P.*, C.*
FROM tblpriority AS P
INNER JOIN tblclient AS C ON C.clientid = P.clientid 
WHERE C.advisor='{$_SESSION[user][id]}' 
AND (
(P.date_oidd LIKE '$date%') OR 
(P.date_ff LIKE '$date%') OR
(P.date_mortidd LIKE '$date%') OR 
(P.date_invest LIKE '$date%') OR 
(P.date_critical LIKE '$date%') OR 
(P.date_mortgage LIKE '$date%') OR 
(P.date_income LIKE '$date%') OR 
(P.date_pmi LIKE '$date%') OR 
(P.date_life LIKE '$date%') OR 
(P.date_pensions LIKE '$date%') OR 
(P.date_ltc LIKE '$date%') OR 
(P.date_savings LIKE '$date%') OR 
(P.date_asu LIKE '$date%') OR 
(P.date_will LIKE '$date%') OR 
(P.date_building LIKE '$date%') OR 
(P.date_contents LIKE '$date%')
) 
ORDER by C.client_lname";

regards

Simon

[r937]

if those columns are DATE datatype, you shouldn't be using LIKE on them

LIKE is for strings, and it's easier if you do date compares as dates, not strings

select P.*
     , C.*
  from tblpriority AS P
inner 
  join tblclient AS C 
    on P.clientid = C.clientid 
 where C.advisor='{$_SESSION[user][id]}' 
   and '$date' 
      in ( P.date_oidd
         , P.date_ff 
         , P.date_mortidd
         , P.date_invest
         , P.date_critical
         , P.date_mortgage
         , P.date_income
         , P.date_pmi
         , P.date_life
         , P.date_pensions
         , P.date_ltc
         , P.date_savings
         , P.date_asu
         , P.date_will
         , P.date_building
         , P.date_contents ) 
order 
    by C.client_lname