[RESOLVED] QUERY question II

[fontener]

I apologize in advance if I do not use the proper terminology 🙂

Ok, so I run the following query:

$result =mysql_query( "SELECT r.*,f.*,m.*, p.*, d.* FROM r, f, m, p, d WHERE r.f_id = f.f_id and r.md_id = m.md_id and r.p_id = p.p_id and r.pd_id = d.pd_id");

now what I want to do is if the f_id from the returned result also exists as f_id in another table (let's call this one e)... it echoes 'active'.. if that f_id does not exist in table e it echoes 'inactive'

thanks!

[dagon]

sounds like you need a JOIN, if not a subquery

[djjjozsi]

you should use readable table names, and if you want to make it simplier you can use aliases.

[Lars_Berg]

select r.*,
       f.*,
       m.*, 
       p.*, 
       d.*,
       case when exists(select * from e where e.f_id = r.f_id) then 'active' else 'inactive' end
  from r, 
       f, 
       m, 
       p, 
       d 
 where r.f_id = f.f_id 
   and r.md_id = m.md_id 
   and r.p_id = p.p_id 
   and r.pd_id = d.pd_id