Correct MySQL JOIN format to avoid nested SELECT

Question

I have two separate SELECT statements:

SELECT VCe.VId FROM `VCe` WHERE `YId` = 9007 AND `MaId` =76 AND `MoId` = 2851

SELECT r_pts.p_id FROM r_pts WHERE r_pts.v_id IN (57202, 57203, 69597, 82261, 82260, 69596, 69595, 82259)

When they are run separately they both complete in under .05sec however when I nest the first one within the second, it dramatically increases to 3.3sec.

I would like to do a join so that I can get the output from the second SELECT using the first select as the result set for the IN() but I cannot figure out how to include WHERE conditions in a JOIN.

Edit: Also what is the correct syntax to do a join as I am requesting?

Thanks for your help, its appreciated!

Answer 1

SELECT r_pts.p_id FROM r_pts, 'VCe' WHERE r_pts.v_id = VCe.VId AND VCe.YId = 9007 AND VCe.MaId =76 AND VCe.MoId = 2851

The basic goal of a join is to describe how the two tables relate. I inferred from your example that the v_id column in the r_pts table was a foreign key pointing to the VId primary key in the VCe table. When you add a term in the query (such as "r_pts.v_id = VCe.VId") that has a field from each table you wish to join, that tells the database how to match up the rows between the tables to make "virtual rows" that contain the columns from both tables. Your other query terms limit which rows are included in the result set.

Answer 2

Equivalent to MattMcKnight's query whilst illustrating "how to include WHERE conditions in a JOIN":

SELECT r.p_id 

  FROM r_pts r

       INNER JOIN VCe v
       ON v.VId = r.v_id
   AND 
       v.YId = 9007 
   AND 
       v.MaId = 76 
   AND 
       v.MoId = 2851