3

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Ian
  • 11,920
  • 27
  • 61
  • 77
  • What...? If this your production code, you open yourself to an SQL Injection attack. – Salamander2007 Nov 25 '08 at 02:27
  • I think Robert Wagner answer to your related question (How is this MySQL query vulnerable to SQL injection?) pretty much sums up my intention. This pattern opens up SQL Injection probability, while other pattern makes it impossible. – Salamander2007 Nov 25 '08 at 04:42

1 Answers1

6

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
  • 121,453
  • 34
  • 200
  • 199