如何通过选择特定列中相关数据的组合来从表中选择行？ MySQL的

Question

I have a table like;

tablea

4c4fedf7    OMoy3Hoa
4c4fedf7    yiWDGB4D
broe4AMb    A9rLRawV
broe4AMb    mi9rLmZW
nhrtK9ce    yEsBoYLj
rEEtK9gt    A9rLRawV
rEEtK9gt    mi9rLmZW
rEEtK9Hh    A9rLRawV
rEEtK9Hh    msBWz8CQ

If I give the input like A9rLRawV,mi9rLmZW. I want an output like;

broe4AMb
rEEtK9gt

The output is generated as a result of A9rLRawV,mi9rLmZW combination. Here, broe4AMb and rEEtK9gt both have A9rLRawV and mi9rLmZW associated in tablea. I made a query, but I get output like;

broe4AMb
rEEtK9gt
rEEtK9Hh

My query is like;

SELECT DISTINCT prodid
FROM tablea
WHERE tagid IN ('A9rLRawV','mi9rLmZW');

The output is like this because I think, reetK9Hh has A9rLRawV associated with it in tablea. But i don't want that entry to appear because it doesn't have mi9rLmZW associated with it.

Here is the SQL fiddle http://sqlfiddle.com/#!9/12223/2/0

Does it require a SELF JOIN. What will be the most 'efficient' method? Is it possible to achieve this with MySQL alone or with support of PHP? How can I do this / fix this?

Answer 1

I firmly believe this can be resolved by SQL alone. Get the data you need from the database. Have PHP do the presentation. Why? Typically the Database can parse data much faster than you can through code on a webserver/middleware server.

As to how... Read up on having clauses and group by:

What I've done here is group by prodID to ensure all prodIDs are returned. mySQL extends the group by clause and while it may allow a having clause to exist without a group by, you will not get the desired results without grouping by prodid. The having a gets a distinct count of both tags requested in the where. Note: I added distinct to the count on tagID as I was unsure if your overall data could have duplicate tagIds for each prodid. we could eliminate it if we know values are distinct.

The # can be dynamically set based on the number of TagIDs provided if needed.

SELECT prodid
FROM tablea
WHERE tagID in ('A9rLRawV','mi9rLmZW')
GROUP BY  prodid
HAVING count(distinct tagID)=2

SQL FIddle

I prefer this approach as it scales better than a self join as you indicated might be needed. Pass in two parameters: one for the tags one for the number of tags. (I do so hope you're using paramaterized queries with your PHP) With self joins you have to write dynamic SQL to handle each additional tag so if you have 3, 4,5 more joins. This way you just pass in the 5 values and the number 5; and get a list back of all those that match.