2

The query below works well. Now I would like to add another table called addresses, which contains the same submissionid and zip variables below. I would like to pull all the submissionid variables from addresses where zip = '$zip', then use these results to look up the corresponding title rows from the submission table in the query below.

$sqlStr = "SELECT title, points, submissionid
             FROM submission 
            WHERE zip = '$zip'
         ORDER BY points DESC, title ASC              
     LIMIT $offset, $rowsperpage";
John
  • 4,820
  • 21
  • 62
  • 92

1 Answers1

4

You can acheve this using subquery

$sqlStr = "SELECT title, points, submissionid
         FROM submission 
        WHERE submissionid IN(
              SELECT submissionid 
              FROM addreses
              WHERE zip = '$zip')
     ORDER BY points DESC, title ASC              
 LIMIT $offset, $rowsperpage";

Reference: IN Subquery

Or with JOIN

$sqlStr = "SELECT s.title, s.points, s.submissionid
         FROM submission s 
         INNER JOIN addresses ad
         on ad.submissionid = s.submissionid
         WHERE ad.zip = '$zip'
     ORDER BY s.points DESC, s.title ASC              
 LIMIT $offset, $rowsperpage";

Reference: JOIN

And as @AdrianCornish mentioned in comment INNER join is faster than subquery. So you should better go for the second option with JOIN.

A related thread on SO Subqueries vs joins

Community
  • 1
  • 1
Prasenjit Kumar Nag
  • 13,391
  • 3
  • 45
  • 57
  • 3
    INNER JOIN is usually much better optimized in my experience with mysql. eg I wrote a query yesterday very similar - 36seconds for the WHERE/IN and 0.06 seconds for the INNER JOIN against a tmp table – Adrian Cornish Jun 02 '12 at 07:14
  • @Joy I thought this worked, but it actually omits results from **submission** – John Jun 05 '12 at 00:08
  • @John Depending on what you want, you may need a different join like `left` or `right`. But thats hard to say without having a look at your data, can you please elaborate how is it omitting data? – Prasenjit Kumar Nag Jun 05 '12 at 05:08