CODEWITHSUNDEEP
mysqljoinlimit
luv2code
luv2code

Reputation: 1296

Limiting to one result on inner join table

Hi I have the following query:

SELECT pr.*, img.image_file, c.contract_id, c.cmin_markup, c.cmeal_plan, c.tax_include, c.civa, m.plan_name, r.room_name, r.room_id FROM contracts AS c
INNER JOIN meal_plan AS m ON m.plan_code = c.cmeal_plan AND m.lang = '1'
INNER JOIN room_infos AS r ON r.hotel_id = '$hid' AND r.lang = '1'

LEFT JOIN images AS img ON img.foreign_id = r.room_id LIMIT 1

LEFT JOIN promotions as pr ON pr.hotel_id = '$hid' AND FIND_IN_SET('c.contract_id', pr.contract_id) > 0 AND FIND_IN_SET('r.room_id', pr.room_id) > 0 AND pr.travel_start <= '$now' AND pr.travel_end >= '$now' AND pr.book_start <= '$arriving' AND pr.book_end >= '$departing' AND pr.lang='1'
WHERE c.hotel_id = '$hid' AND c.cstart <= '$arriving' AND c.cend >= '$departing' AND c.ctype = '1'"

I need to limit the images table to give me only one result. I am not sure if this is possibe or how would be the best way to achieve this. If more information is needed please let me know. It is a lot of tables to post. Thanks in advance for any help!!

Upvotes: 2

Views: 2421

Answers (2)

Shane Fright
Shane Fright

Reputation: 385

Include a group by to the end of your query. Assuming your images table has an ID field simply append "GROUP BY images.id"

Upvotes: 2

Hank
Hank

Reputation: 121

I don't have mySql instance in front of me to try this but consider moving images into a subquery:

LEFT JOIN (select * from images LIMIT 1) AS img on img.foreign_id = r.room_id

This of course is not very efficient on its own as there is no where clause in the inline subquery, you can improve the subquery to be more restricting and have it join with the parent query.

Something like this:

select * from images as img2 where img2.foreign_id = r.room_id LIMIT 1

Upvotes: 0

Related Questions