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

Could you please post your sample data and the expected result? — PM 77-1
– PM 77-1, Commented Apr 2, 2013 at 17:38
@PM77-1 77-1 edited for the sample data and stem belongs to the annotations table — user2233286
– user2233286, Commented Apr 2, 2013 at 17:45
Do you need a count of any two of the stems? Did I get it right? — PM 77-1
– PM 77-1, Commented Apr 2, 2013 at 17:56
@PM77-1 yeah a count of any 2 stems. So if I had another entry by user id 4, with image/ taxi, then the count would be 2, as user id 1 too has these stems — user2233286
– user2233286, Commented Apr 2, 2013 at 17:59

Akash · Accepted Answer · 2013-04-02 18:42:48Z

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

edited Apr 2, 2013 at 18:42

answered Apr 2, 2013 at 18:04

Akash

5,02212 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Akash Over a year ago

It gives a count of total stems of a particular user

Akash Over a year ago

Ok, also it seems that your requirment is different, of getting users that have stems, where the stem of a particular user is also used by some other user, right?

user2233286 Over a year ago

Also, I'm afraid your understanding is incorrect. I'm not looking for to fetch female users that have 2 or more stems. I'm looking to do exactly as my example shows, and find occurrences of two stems together.

Akash Over a year ago

@user2233286 I have updated the answer, please let me know if its helpful

user2233286 Over a year ago

again this does not produce what I need, as there is no count. I cannot explain it any better than my original question. Other than: You and I have 3 stems each, and we're both female. 2/3 stems each are the same. Therefore that count is 2. I want to display the stems along with its count. If we add another user who also has the same attributes. Therefore the count is 3. However if your second and third stem is the same as their second and third stem, that count is 2. I hope this clearly explains to you what I'm trying to do

|

Ali Gangji · Accepted Answer · 2013-04-02 20:43:16Z

1

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

edited Apr 2, 2013 at 20:43

answered Apr 2, 2013 at 18:04

Ali Gangji

1,51113 silver badges22 bronze badges

3 Comments

user2233286 Over a year ago

I like what this produces. However, the count I'm slightly confused about. Does it count how many users have named the same group_concat?

user2233286 Over a year ago

this counts how many stem each user has given not their co-occurrence

Ali Gangji Over a year ago

I've updated my answer. This will now produce a list showing combinations of 2 stems, and how many users have each combination.

PM 77-1 · Accepted Answer · 2013-04-02 20:42:58Z

0

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

edited Apr 2, 2013 at 20:42

answered Apr 2, 2013 at 20:08

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

8 Comments

user2233286 Over a year ago

this provides me with a column of stem 1 with entries of letter a for some reason :S would this be down to the fact that I haven't specified the image_id? How would you I specify the image_id within your code?

PM 77-1 Over a year ago

Unless you have various image_id values in your data my omittion shouldn't matter.

user2233286 Over a year ago

I have 5 image_ids and would like to query them separately

PM 77-1 Over a year ago

Please check my demo (link in the post). If it performs alright I'll update for image_id. I do not have access to MySql server, so SQLFiddle.com is my only playground.

user2233286 Over a year ago

It performs correctly. But now I need to specify image_id=1 and user.gender=female. How would I add this into your code so I can see if it works on my actual db

|

Collectives™ on Stack Overflow

MySQL - loop through rows

3 Answers 3

11 Comments

3 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

3 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related