JOIN with probable GROUP clause

Hi,

I've got two tables (P and R) joined by an ID column. Table P also has a TYPE column which indicates the TYPE (1 of about 100) of the entry indicated by the ID.
Table R also have a column called REF which indicates the files which the ID was loaded from.

What I need to know is how I can join P and R in order to give me the files (REFs) where more than one TYPE of ID was in the file.
Note that there can be more than one of the same ID in Table R but there will only be one of it in Table P.

Its a friday afternoon and my brain is fried....HELP!!!!

I need this to be as efficient as possible.
Bear in mind Table R has over 35,000,000 rows and Table P has about 30,000

E.g.
Table P
ID TYPE
1 a
2 a
3 b
4 b
5 b

Table R
ID REF
1 aa
1 aa
2 aa
2 aa
3 aa
4 bb
5 bb
1 cc
1 cc
1 dd

In the above example I would be returned REF 'aa' because it contained IDs 1, 2 and 3 which are of TYPEs 'a' and 'b'. Whereas REF 'bb' only contained IDs with a TYPE 'b' and I want the ones where there is more than one TYPE in a a file(REF).

Does this SQL give me what I want?
SELECT r.REF FROM R as r JOIN P as p ON p.ID=r.ID group by r.REF HAVING count(p.TYPE) > 1

Tags: SQL, MySQL