SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a matching field from table

    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 ?

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •