user580950 user580950 - 5 months ago 8
SQL Question

Mysql Select Query without PHP

I have written following select query which returns the required records.I know how to code this in PHP. I am wondering if it is possible in one single select mysql query

SELECT annual_end_date
,email
,expiry_id
,status
,verify
,email
,password
FROM Users
WHERE email = 'test@test.com'
AND password = 'password'


On the same table I would also like to check (psuedo code PHP below), return expiry_id=0 records and
annual_end_date
is a
date
Column is less
then current date


If expiry_id='0' THEN
check annual_end_date column
If annual_end_date is less then the current date


If expiry_id is not equal to '0' it should check
subscription table
and compare
pay_end_date
column

If expiry_id!='0'
check pay_end_date column in subscription table
If pay_end_date is less then the current date then show records


Subscription table has just one field
pay_end_date
with is a
date column

Answer

You can add something like this to your query...

 AND (  ( expiry_id='0' AND annual_end_date < DATE(NOW()) )
     OR ( expiry_id<>'0' AND EXISTS ( SELECT 1
                                        FROM subscription s
                                       WHERE s.user_id = Users.user_id -- ?
                                         AND s.pay_end_date < DATE(NOW())
                                    )
        )
     )   

The specification says to check the "subscription table", but no indication is given about the relationship between subscription and Users. The subquery is based on a guess about the foreign key column in subscription, and the referenced column in Users.