views:

515

answers:

1

I need to modify the following MySQL statement to include information from a third table... but I suck at joins.

select
 ss.*,
 se.name as engine,
 ss.last_run_at + interval ss.refresh_frequency day as next_run_at,
 se.logo_name 
from 
 searches ss join search_engines se on ss.engine_id = se.id
where
 ss.user_id='.$user_id.'
group by ss.id
order by ss.project_id, ss.domain, ss.keywords

I need to also include retrieving projects.project_name by matching searches.project_id to projects.id.

Thanks!

+3  A: 

Check out SELECT Syntax and JOIN Syntax.

But, simplest answer -- add another JOIN..ON statement.

select
        ss.*,
        se.name as engine,
        ss.last_run_at + interval ss.refresh_frequency day as next_run_at,
        se.logo_name,
        pr.project_name -- +
from 
        searches ss
        join search_engines se on ss.engine_id = se.id
        join projects pr on ss.project_id = pr.id -- +
where
        ss.user_id='.$user_id.'
group by ss.id
order by ss.project_id, ss.domain, ss.keywords

[EDIT] I added the -- + comments to point out my additions.

Jonathan Lonowski
what's that -- + doing there?
Salamander2007