We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to limit by x within a join statement

vidda22
vidda22 asked
on
Medium Priority
441 Views
Last Modified: 2012-05-11
Hi guys!
I am getting currency prices out of the database.
Every company has got at least 10 prices.
I would like to limit to 4 per company.

SELECT `b`.`name`,`bc`.`id` `idBusinessCategory`,`bc`.`idCategory`,`b`.`id` `idBusiness`,`bpp`.`tradeProductName`,`bpp`.`price_buying`,`bpp`.`price_selling`
FROM business_categories bc JOIN business b ON bc.idBusiness JOIN business_price_plan bpp
 ON bc.id=bpp.idBusinessCategory JOIN city c ON b.idCity=c.id WHERE c.id=1 AND b.displayPricePlan=1 ORDER BY `bc`.idCategory ASC, `b`.id ASC

Open in new window

if i type in LIMIT 4 at the end of the statement I will get 4 results from 1 company
but there is more companies, how do I do that?
Hope I am clear enough, otherwise please ask.
Thanks for help!
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
So it would be better if I store business id in my php variable while looping through the results?
And than after 4 results from one company start skipping, until company id changes into next company?

please tell me what does this do:
 AND 4 <= ( select count(*) from  business_price_plan x WHERE x.idBusinessCategory = bc.id AND x.id <= bpp.id )

after querying the code you passed to me sql came back with only one result per company?

Author

Commented:
@kalmax
after querying it came back with this error?

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bc.id in (SELECT bpp.idBusinessCategory FROM business_categories bc RIGHT JOIN  ' at line 4
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
@kalmax thanks so much for trying!
that is what mysql comes back with
[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

should I rather do it in php?

Author

Commented:
@angelIII
Have you got any idea why it comes back with only one item per query?
thanks for help.

Author

Commented:
if you can guys please recommend me some reading, so I can understand it myself.

Many thanks!
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.