Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: a field from the same table

    hi to all,
    anyone knows how to make this work?
    it gives me an error.
    i want a field from the same table with where condition
    Code:
    SELECT
    PawnTicketNo
    ,LastName
    , DateLoan
    , DateRedeem
    ( select DateLoan from pawnshop_tbl where servicetype = 'renew' )
    FROM pawnshop_tbl
    thanks in advance.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by homer.favenir
    anyone knows how to make this work?
    • You seem to be missing a comma after DateRedeem.
    • I think you also want to be using the if() function rather than the sub query.
    • The subquery could return more than one row at any point which I'm guessing is not what your after.

    Mike

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    The subquery could return more than one row at any point which I'm guessing is not what your after.
    yes it is,
    I think you also want to be using the if() function rather than the sub query.
    yes, youre right. instead of using subquery, i should use if() statement.

    Code:
    SELECT
    PawnTicketNo
    ,LastName
    , DateLoan
    , DateRedeem
    , if( ServiceType = 'Renew', DateLoan, '' ) as 'Date Renew'
    FROM pawnshop_tbl p;
    thanks a lot!
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a CASE expression, which is standard SQL, not IF, which is proprietary to mysql
    Code:
    CASE WHEN ServiceType = 'Renew' THEN DateLoan END as 'Date Renew'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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