MySQL - loop through rows

Question

I have the following code

select count(*)
from (select Annotations.user_id
from Annotations, Users
where Users.gender =  'Female'
and Users.user_id = Annotations.user_id
and image_id = 1
group by Annotations.user_id
having sum(case when stem = 'taxi' then 1 else 0 end) > 0 and
       sum(case when stem = 'zebra crossing' then 1 else 0 end) > 0
) Annotations

It produces a count of how many females who have given the stem 'taxi' and 'zebra crossing' for image 1.

Sample data

 user id, image id, stem
 1           1      image
 1           1      taxi
 1           1      zebra crossing
 2           1      person
 2           1      zebra crossing
 2           1      taxi
 3           1      person
 3           1      zebra crossing

Expected result (or similar)

stem1,  stem2,            count
taxi ,  zebra crossing      2
person, zebra crossing      2

However, as there are over 2000 stems, I cannot specify them all.

How would I go around looping through the stem rows with the image_id = 1 and gender = female as opposed to specifying the stem string?

Thank you

Answer 1

As per my understanding, you need to fetch female users that have 2 or more stems

Update: It seems you need to display the user's that have a stem that is used by another user too, I have updated the query for the same

 SELECT
    distinct a.user_id,
    group_concat(DISTINCT a.stem ORDER BY a.stem)
 FROM
    Annotations a 
    JOIN Users u ON ( a.user_id = u.user_id AND u.gender =  'Female' )
    JOIN 
        (
            SELECT 
                b.user_id,
                b.stem
            FROM
                Annotations b
        ) AS b ON ( a.user_id <> b.user_id AND b.stem = a.stem )
 WHERE
    a.image_id = 1
GROUP BY
  a.user_id

Answer 2

UPDATE: As I understand it, you want to select all combinations of 2 stems, and get a count of how many users have that combination of stems. Here is my solution:

SELECT stem1, stem2, count(*) as count FROM
(
    SELECT a.user_id,a.image_id,a.stem as stem1,b.stem as stem2
    FROM Annotations a JOIN Annotations b
    ON a.user_id=b.user_id && b.image_id=a.image_id && a.stem!=b.stem
    JOIN Users ON Users.user_id = a.user_id
    WHERE Users.gender = "Female"
) as stems GROUP BY stem1, stem2 having count > 1 WHERE image_id=1;

The caveat here is that it will return 2 rows for each combinations of stems. (The second occurrence will have the stems in reverse order).