a field from the same table

homer.favenir · #1 (**permalink**) 01-19-09, 04:30

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.

mike_bike_kite · #2 (**permalink**) 01-19-09, 04:37

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

homer.favenir · #3 (**permalink**) 01-19-09, 04:55

Quote:

The subquery could return more than one row at any point which I'm guessing is not what your after.

yes it is,

Quote:

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!

r937 · #4 (**permalink**) 01-19-09, 08:15

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'

Internet Services

Web Development

Digital Marketing

Consumer Tech