MySql - Joining another table with multiple rows, inserting a query into a another query?

Question

I've been racking my brain for hours trying work out how to join these two queries..

My goal is to return multiple venue rows (from venues) based on certain criteria... which is what my current query does....

SELECT venues.id AS ven_id, 
   venues.venue_name, 
   venues.sub_category_id, 
   venues.score, 
   venues.lat, 
   venues.lng, 
   venues.short_description, 
   sub_categories.id, 
   sub_categories.sub_cat_name, 
   sub_categories.category_id, 
   categories.id, 
   categories.category_name, 
   ((ACOS(  SIN(51.44*PI()/180)*SIN(lat*PI()/180) + COS(51.44*PI()/180)*COS(lat*PI()/180)*COS((-2.60796 - lng)*PI()/180)) * 180/PI())*60 * 1.1515) AS dist

FROM venues, 
  sub_categories, 
  categories 
WHERE 
  venues.sub_category_id = sub_categories.id 
  AND sub_categories.category_id = categories.id 
HAVING 
  dist < 5 
ORDER BY score DESC 
LIMIT 0, 100

However, I need to include another field in this query (thumbnail), which comes from another table (venue_images). The idea is to extract one image row based on which venue it's related to and it's order. Only one image needs to be extracted however. So LIMIT 1.

I basically need to insert this query:

SELECT 
  venue_images.thumb_image_filename, 
  venue_images.image_venue_id,  
  venue_images.image_order 
FROM venue_images 
WHERE venue_images.image_venue_id = ven_id //id from above query
ORDER BY venue_images.image_order 
LIMIT 1

Into my first query, and label this new field as "thumbnail".

Any help would really be appreciated. Thanks!

does each image have an unique id? something like `venue_images.id` ? — fthiella, Nov 06 '12 at 20:52
yes venue_images.image_id, venue_images.image_venue_id is a field which contains the venue_id (venue) it relates to. — Danny, Nov 06 '12 at 20:55

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

First of all, you could write the first query using INNER JOIN:

SELECT
  ...
FROM
  venues INNER JOIN sub_categories ON venues.sub_category_id = sub_categories.id 
  INNER JOIN categories ON sub_categories.category_id = categories.id
HAVING
  ...

the result should be identical, but i like this one more.

What I'd like to do next is to JOIN a subquery, something like this:

...
INNER JOIN (SELECT ... FROM venue_images 
              WHERE venue_images.image_venue_id = ven_id //id from above query
              ORDER BY venue_images.image_order 
              LIMIT 1) first_image

but unfortunately this subquery can't see ven_id because it is evaluated first, before the outer query (I think it's a limitation of MySql), so we can't use that and we have to find another solution. And since you are using LIMIT 1, it's not easy to rewrite the condition you need using just JOINS.

It would be easier if MySql provided a FIRST() aggregate function, but since it doesn't, we have to simulate it, see for example this question: How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?

So using this trick, you can write a query that extracts first image_id for every image_venue_id:

SELECT
  image_venue_id,
  SUBSTRING_INDEX(
    GROUP_CONCAT(image_id order by venue_images.image_order),',',1) as first_image_id
FROM venue_images
GROUP BY image_venue_id

and this query could be integrated in your query above:

SELECT
  ...
FROM
  venues INNER JOIN sub_categories ON venues.sub_category_id = sub_categories.id 
  INNER JOIN categories ON sub_categories.category_id = categories.id
  INNER JOIN (the query above) first_image on first_image.image_venue_id = venues.id
  INNER JOIN venue_images on first_image.first_image_id = venue_images.image_id
HAVING
  ...

I also added one more JOIN, to join the first image id with the actual image. I couldn't check your query but the idea is to procede like this.

Since the query is now becoming more complicated and difficult to mantain, i think it would be better to create a view that extracts the first image for every venue, and then join just the view in your query. This is just an idea. Let me know if it works or if you need any help!

edited May 23 '17 at 11:43

Community

1
1

answered Nov 06 '12 at 21:48

fthiella

48,073
15
90
106

Thanks so much for your help and detailed description. I've rewrote the first part of my query, and like you said it returns exactly the same results. However, when I try and add in the second query, it doesn't error, however it doesn't produce any results. What do you mean when you say "create a view", what is this and how can it be achieved? – Danny Nov 07 '12 at 22:39
Just for reference, this is now my entire query... https://docs.google.com/document/d/1hE-ONNCtctI-SrPJOx_YwPmygIxRqDumpFQ4boIpfOQ/edit – Danny Nov 07 '12 at 22:46
if you run just the second query, does it show the correct result? it returns, for every image_venue_id it shows correctly the first image id? if it's correct, maybe there's something wrong with some join... – fthiella Nov 07 '12 at 23:04
a VIEW is a stored query, you give a query a name, and you can reference it (more or less) like it's a table. e.g. `CREATE VIEW first_image AS Select ...etc...` then you can reference it in other queries with the name `first_image` – fthiella Nov 07 '12 at 23:09
if you use INNER JOIN like i suggested you, you should remove your WHERE clause (unless you need to filter something more), i can see that the conditions on the WHERE clause are different than those on the ON clause, is there a particular reason for this? I suspect this might be the problem – fthiella Nov 07 '12 at 23:22
I ran the second query, syntax is correct, although it doesn't return anything. Therefore, I suspect this maybe the problem. I have removed the WHERE clause for now (which still works), however I will need this at some point for other conditions, like "WHERE venues.active = 1"... For reference, my venue_images table looks like this.. http://i45.tinypic.com/359emmd.png and my new SQL looks like.. https://docs.google.com/document/d/1wa2b-zgcC2tvHCnD5uvyEFRM7Vy9ftKKD_vBlU8j2qU/edit - Thank you for your help :) – Danny Nov 08 '12 at 12:16
Okay, after playing around. The above queries do produce results! (the reason was because there were no images). The only problem left is that when there are no images for a particular venue, it doesn't show the venue row at all. Is it possible to still show the venue row, however have the new image_id/image_thumb_filename field blank (if there aren't any images)? Thank you! Nearly there! – Danny Nov 08 '12 at 14:40
Okay, I changed the last two "inner joins" into "left joins", and this fixed my problem. My final code was (https://docs.google.com/document/d/1wa2b-zgcC2tvHCnD5uvyEFRM7Vy9ftKKD_vBlU8j2qU/edit). Thanks for your help! – Danny Nov 08 '12 at 14:53
glad to help you... :) yes you could add some other where conditions but if you move your condition on the INNER JOIN...ON you won't need them... and yes all you need here is a LEFT JOIN :) – fthiella Nov 08 '12 at 16:00

score 0 · Answer 2 · answered Nov 06 '12 at 20:35

0

I'm not too sure about your data but a JOIN with the thumbnails table and a group by on your large query would probably work.

GROUP BY venues.id

answered Nov 06 '12 at 20:35

Shawn

3,583
8
46
63

MySql - Joining another table with multiple rows, inserting a query into a another query?

2 Answers2