MySQL: Selecting foreign keys with fields matching all the same fields of another table

From what I'm reading, this is the sort of thing that is meant to be done with a division operator, but apparently that is not implemented in MySQL. Basically, my setup is a table with two columns where keys are listed multiple times with different listings in the secon开发者_如何转开发d column, e.g.

PID | GID
A1  | G1
A1  | G2
A2  | G1
A2  | G3
A3  | G1
A3  | G2
A4  | G2
A4  | G3

Basically I have to find a subset of the table based on PID and all the GIDs associated with it, e.g. all GIDs that A3 is assigned, which is fairly easy to get, producing a intermediate table

PID | GID
A3  | G1
A3  | G2

But what's giving me trouble is figuring out how to select the records in the original table that match ALL of the GIDs for the subset table. As I said, everything I've been able to find points towards a division operator, but that doesn't exist in MySQL, so I'm kind of at the end of my rope. I can't figure out a way to join or do an operation that matches all GIDs - only partial matches, which isn't what I'm looking for. Any help? The book I'm looking at isn't particularly helpful.

So you have a list of GIDs and you want all the PIDs that have all the GIDs in the list?

Given that (e.g. in your procedural language) you can determine how many GIDs you have, a rather strange but feasible way is this:

SELECT FROM the_table WHERE GID IN ('G1','G2') GROUP BY PID HAVING COUNT(*) = 2

I'm not 100% sure this is what you're after since your question is a bit confusing, but we'll see…

You appear to be after a subquery:

SELECT pid, gid FROM your_table WHERE gid in (SELECT gid FROM your_table WHERE pid = 'A3')

That'll select all of the rows from your table where the GID matches any of the GIDs that are associated with the PID 'A3'. You might need to read that over again slowly…it's a mouthful.