Select Query in sql with testing on each row

Question

i have the following query:

SELECT favorite_id,MO,name,b.image_id,image_path FROM buddies b,images i where reg_id=@regID and b.image_id=i.image_id

the field b.image_id in buddies should b tested before getting the image_path from the table images... if the image_id is 0 i have to the the image_id from a table registration and the get the image_path from the table image and if the image_id is different than 0 i directly get the image_id from the table images... so when selecting the favorite_id and the image_id from table buddies i need to loop on this table for every row and test the image_id

can anyone help me?

Answer 1

SELECT favorite_id,MO,name,
b.image_id,
image_path = case when b.image_id ='0' then i1.image_path else i.image_path end

FROM buddies b, registration r 
left join images i  on b.image_id=i.image_id
left join images i1 on r.image_id=i1.image_id
where b.reg_id=@regID and  r.reg_id=@regID 

Answer 2

SELECT favorite_id,MO,name,b.image_id,
       case b.image_id when 0 then i.image_path else b.image_path end as image_path
  FROM buddies b,images i 
  where reg_id=@regID and b.image_id=i.image_id

Smth like that...

If you need additional conditional select -- then it's here: SELECT CASE WHEN THEN (SELECT)