如何执行此MySQL连接操作？

I have two tables like below;

tablea

tableb

In tablea, I have prodid and tagid. Its a composite primary key. prodid acts as a foreign key, pointing to tableb. In tableb, I have prodid, form, and link. prodid of tablea points to link of tableb. What I want is, if I give input like array, like, A9rLRawV,mi9rLmZW , I want output as;

broe4AMb
rEEtK9gt

I have a query like;

SELECT p.link AS links
FROM tablea t
INNER JOIN tableb p ON t.prodid = p.link
WHERE t.tagid IN ('A9rLRawV','mi9rLmZW')
GROUP BY p.prod HAVING COUNT(*) > 1

But, it returns result like;

broe4AMb
rEEtK9gt
rEEtK9Hh

But what I expect should not contain rEEtK9Hh. I am expecting the result;

broe4AMb
rEEtK9gt

This is because, I want product, which has both A9rLRawV and mi9rLmZW. Here, rEEtK9Hh (or prod Bhc5y488) in table b has only A9rLRawV, which I don't want.

How can I do this? I use PHP + MySQL

FIRST APPROACH [SOLVED]:

Your error is here:

INNER JOIN tableb p ON t.tagid = p.link

You need to use your JOIN with prodid, something like this:

INNER JOIN tableb p ON t.prodid = p.link

SECOND APPROACH:

You don't need to group the 'prod' field because always are unique on 'tableb'. You need to group with 'link' field.

Alternatively if you just look at your tableA you could directly retrieve the prodid by using the GROUP_CONCAT() function in MySQL.

For example produce a virtual SELECT table where you can directly compare on the existing pair (keypair in this example).

SELECT prodid, GROUP_CONCAT(tableA.tagid) as keypair FROM `tableA` GROUP BY prodid;

Would return:

prodid    keypair
--------  -----------------
4c4fedf7  OMoy3H0a,yiWDGB4D
broe4AMb  A9rLRawV,mi9rLmZW
nhrtK9ce  yEsBoYLj
rEEtK9gt  A9rLRawV,mi9rLmZW
rEEtK9Hh  A9rLRawV,msBWz8CQ

The FINAL query could be something like:

SELECT prodid 
   FROM (SELECT prodid, GROUP_CONCAT(tableA.tagid) as keypair FROM `tableA` GROUP BY prodid) AS T 
   WHERE keypair = 'A9rLRawV,mi9rLmZW'

Returning:

prodid
--------
broe4AMb
rEEtK9gt