How to query a second table for each result from a first table?

Question

I'm not really sure how to do this - I only posses a limited knowledge of joins in MySQL, and from what I read I don't think they'd be of much use here.

Essentially, I have 2 tables:

images                          votes
---------------------------     ------------------------------
image_id | name | square_id     vote_id | image_id | vote_type 
---------------------------     ------------------------------
1          img1   14            1         4          1
2          img2   3             2         17         0
3          img7   72            3         2          1
...                             ...
n          imgn   1478          n         n          1

What I'd like to do is get the details of each image and the number of votes cast (plus the vote_type) on each image where a certain condition is true (such as where each image has a certain square_id). Executing the first part of this query is easy:

SELECT `image_id`, `name` FROM `images` WHERE `square_id` = :boundParameter;

But, I'm unsure of how to get each vote_id and vote_type for each image that meets the original WHERE condition in my query.

How would I accomplish this?

Welcome into the world of JOINs. You're bound to enter in a world of awesomeness on one hand and great pains on the others. A good start is here: http://dev.mysql.com/doc/refman/5.0/en/join.html — STT LCU, Aug 30 '13 at 10:16
What pains? An even better start is here: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — fancyPants, Aug 30 '13 at 10:17
Joins aren't often easily grasped and mastered by novices. At least, this is my experience. — STT LCU, Aug 30 '13 at 10:22

score 4 · Accepted Answer · edited May 23 '17 at 11:57

4

This is actually a really simple join between tables. You really, really should read this Q&A that I put together about SQL and queries and joins between two tables.

In the meantime, this will probably give you what you want:

select
    img.image_id,
    img.name,
    img.square_id,
    count(vot.vote_id) as numberVotes,
    vot.vote_type
from
    images img
        join votes vot
            on img.image_id=vot.image_id
group by
    img.image_id,
    img.name,
    img.square_id,
    vot.vote_type

So, with that, first off, you don't want multiple queries, you want to use one query here. Running multiple connections/queries to a database is a MUCH higher overhead than running one query that fetches all the data you want.

edited May 23 '17 at 11:57

Community

1
1

answered Aug 30 '13 at 10:19

Fluffeh

33,228
16
67
80

Thanks for the link and the explanation! – marked-down Aug 30 '13 at 10:36
@Antilogical No problems, i know the link is around a thousand pages long, but really, take the time to read it - at the very least, the very first answer - I have the code there so that you can create the database and run/test the queries so that you really truly understand what is going on. – Fluffeh Aug 30 '13 at 10:39

score 0 · Answer 2 · answered Aug 30 '13 at 10:20

0

Try it like this:

SELECT images.image_id, images.name , votes.vote_id , votes.vote_type 
FROM images
LEFT JOIN votes ON images.image_id = votes.image_id
WHERE `square_id` = :boundParameter;

answered Aug 30 '13 at 10:20

Andy

2,892
2
26
33

score 0 · Answer 3 · answered Aug 30 '13 at 10:21

0

SELECT images.image_id,
       images.name,
       votes.vote_id,
       votes.vote_type
FROM images
LEFT JOIN votes ON images.image_id = votes.image_id
WHERE images.square_id = :boundParameter;

answered Aug 30 '13 at 10:21

Tahir Yasin

11,489
5
42
59

How to query a second table for each result from a first table?

3 Answers3