select data from 1 table based on criteria from another table

[wakk0]

is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

[dadane]

SELECT * FROM message_therapist WHERE therapist_id IN (SELECT therapist_id FROM table2 WHERE message_scedule.finish > 0)

[r937]

SELECT message_therapist.* 
  FROM message_therapist 
INNER
  JOIN massage_schedule
    ON massage_schedule.therapist_id = message_therapist.therapist_id 
   AND massage_schedule.finish > 0

[dadane]

Which one will be more efficient?

I would believe mine is, - but I don't really know much about MySQL, - but making the join will make a big internal table, - that really isn't needed. Will it not?

[dadane]

Mine with right syntax will be something like

SELECT *
FROM message_therapist
WHERE therapist_id IN
  (SELECT therapist_id
   FROM message_schedule
   WHERE finish > 0)

[r937]

but making the join will make a big internal table, - that really isn't needed. Will it not?

that is correct, it will not

do an EXPLAIN on both queries to see how the optimizer handles them

many times, a query with a subquery is actually converted into a join

[wakk0]

thanks dadane...works perfectly.