CODEWITHSUNDEEP
mysqllimit
aknessy
aknessy

Reputation: 55

How to use LIMIT with INNER JOINS?

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!

Upvotes: 1

Views: 140

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

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)

Upvotes: 1

Related Questions