1

I have read some of the FAQs based on these question but I seem not to have found one that relates to what I wish to do. Am not very proficient with MYSQL at all, especially when it comes to complex queries and subqueries. I have Three tables I created to store references to images uploaded to a folder via php, the structure of this tables looks like this;
1.) For the main image table: 

productImgTable: 
 ------- -------- ------------------- 
|ImgID|ProdID|   ImgURL.    |  
 ------- --------- ------------------ 
| 1.     | Xc332| 2_Xc332.jpg|
 ------- --------- ------------------ 
| 2.     |Yt3f4.  |5_Yt3f4.jpg.|
 ------- --------- ------------------

For the first thumbnail table (thumbLarge)

 ------------- ----------- ------------------ 
| thumbID|  ProdID |   ImgURL.    |  
 ------------ ------------ ------------------ 
|   1        |  Xc332  | 2_Xc332.jpg |
 ----------- ------------ ------------------ 
| 2.         |  Yt3f4   |5_Yt3f4.jpg  |
 ---------- ------------- ------------------

For the second thumbnail table(ThumbSmall), I have:

 ----------- ----------- ------------------ 
|thumbID|  ProdID|   ImgURL.    |  
 ----------- ----------- ------------------ 
| 1         | Xc332   | 2_Xc332.jpg|
 ---------- ------------ ------------------ 
| 2.        | Yt3f4    |5_Yt3f4.jpg. |
 ------- -------------- ------------------

The thumbnails table(Small & Large) have a Foreign Key column prodID referencing the main Image table. Now I'd like to be able to select one ImgUrl from the productImgTable, 2 imgUrl from thumbSmall Table and finally 1 imgUrl from the thumbLarge Table. This is because a product can have multiple thumbnails as well as multiple main images. So the tables have a many-to-many relationship so to speak.

I did something like:

SELECT pi.prodID, pi.imgUrl
FROM productImage AS pi
INNER JOIN(SELECT tL.imgUrl FROM thumbLarge AS tL ON 
tL.imgID = pi.ImgID LIMIT 1)
INNER JOIN(SELECT ts.imgUrl FROM thumbSmall AS ts ON
ts.imgID = pi.imgID LIMIT 2)
WHERE prodID = ...
LIMIT 1

My lame attempt didn't yield any results. So please what is the right way to do this? Are the INNER JOINs really necessary? I know there are obviously more sensible and professional ways of doing this, can someone show how? Thanks! I already know my attempt is lame and MYSQL is probably rolling on the floor right now with laughter!

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
aknessy
  • 55
  • 8
  • you cant use LIMIT in joins – echo_Me May 26 '14 at 16:29
  • Your question aside, arent' the thumbnails smaller versions of the big images? If so, wouldn't you want to store them in a single table, either in three different columns, or maybe not even store the thumbnail names at all, but derive them from the large image name. – GolezTrol May 26 '14 at 16:29
  • Perhaps this may help: http://stackoverflow.com/questions/6879391/mysql-join-with-limit-1-on-joined-table – Mike May 26 '14 at 16:32
  • Thanks for that suggestion @GolezTrol. I wonder why I never considered that. Anyway I have implemented your suggestion and it's a lot better than creating multiple Tables for the thumbnails. Like you said; I think it was a bit strange to store the thumbnails in a separate table, it turns out that I never required those extra overheads on my database! Thanks for pointing that out. – aknessy May 28 '14 at 18:22

1 Answers1

1

Are you looking for UNION?

SELECT pi.prodID, pi.imgUrl
FROM productImage AS pi
WHERE prodID = ...
LIMIT 1
UNION ALL
(SELECT pi.prodID, tL.imgUrl 
FROM thumbLarge AS tL 
WHERE prodID = ...
LIMIT 1)
UNION ALL
(SELECT pi.prodID, ts.imgUrl 
FROM thumbSmall AS ts 
WHERE prodID = ...
LIMIT 2)
GolezTrol
  • 114,394
  • 18
  • 182
  • 210