mysql subquery  & case statement with a default value

Candidochris
Candidochris used Ask the Experts™
on
I was looking for a way to do a subquery in a MYSQL query, such as:

SELECT Name, Age, State, Rank, (CASE SELECT Last_Years_Rank FROM 2009  WHERE Name='Name' IF NOT EXISTS THEN 0)  AS Last_Year_Rank FROM 2010 WHERE Name='Frank Jenkins'

Basically looking to pull data from a second table if it exists, and 0 if it does not. I have a good understanding of mysql, but combining a subquery w/ CASE statements is throwing me. I'd like it to return 0 if the customer is not in the 2009 database (so that adding the ranks only accounts for 2010).

Thanks in advance for any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
how about

select 2010.Name, 2010.Age, 2010.State, 2010.Rank, coalesce(2009.Rank,0) AS Last_Year_Rank
from 2010 
  left outer join 2009 on 2009.name = 2010.name
where Name='Frank Jenkins'

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
note: you should use ` for table names with just numbers ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial