CODEWITHSUNDEEP
mysqlsqldatabaseloopsrows
user2233286
user2233286

Reputation:

MySQL - loop through rows

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

Upvotes: 3

Views: 1480

Answers (3)

Ali Gangji
Ali Gangji

Reputation: 1503

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).

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

Here's my attempt to solve your problem:

SELECT COUNT(*) AS Count, a1.stem AS Stem1, a2.Stem AS Stem2 
FROM Annotations AS a1
INNER JOIN Annotations AS a2 ON a1.user_id = a2.user_id AND a1.image_id = a2.image_id
                          AND a1.stem < a2.stem 
WHERE a1.image_id = 1
GROUP BY a1.stem, a2.Stem
HAVING COUNT(*) > 1;

I did not include image_id logic.

Please see my SQL Fiddle here: http://sqlfiddle.com/#!2/4ee69/33

Based on the following data (copied from yours) I get the result posted underneath it.

CREATE TABLE Annotations
    (`user_id` int, `image_id` int, `stem` varchar(14))
;

INSERT INTO Annotations
    (`user_id`, `image_id`, `stem`)
VALUES
    (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')
;


  COUNT STEM1   STEM2
    2   person  zebra crossing
    2   taxi    zebra crossing

Upvotes: 0

Akash
Akash

Reputation: 5012

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

Upvotes: 1

Related Questions