CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 4944

Changing query to add results from secondary table

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";

Upvotes: 2

Views: 59

Answers (1)

Prasenjit Kumar Nag
Prasenjit Kumar Nag

Reputation: 13461

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

Upvotes: 4

Related Questions