[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

How to limit by x within a join statement

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!
0
vidda22
Asked:
vidda22
  • 5
  • 3
  • 2
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are very clear.
unfortunately, with MySQL, you could do it, but it would be a very inefficient query.
you will be better off doing the "limit" in the front-end application code.

anyhow, I have to presume that the "limit 4" is to be applied on the  business_price_plan  table, and that the table has an ID column (or some other column to "sort by") .
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 
 AND b.displayPricePlan=1 
JOIN business_price_plan bpp
  ON bc.id=bpp.idBusinessCategory 
 AND 4 <= ( select count(*) from  business_price_plan x WHERE x.idBusinessCategory = bc.id AND x.id <= bpp.id )
JOIN city c 
  ON b.idCity=c.id 
 AND c.id=1 
ORDER BY `bc`.idCategory ASC, `b`.id ASC

Open in new window

0
 
KalpanCommented:
please modify as below

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 city c ON b.idCity=c.id WHERE c.id=1
bc.id in (SELECT bpp.idBusinessCategory FROM business_categories bc RIGHT JOIN  business_price_plan bpp ON bc.id=bpp.idBusinessCategory LIMIT 4)
AND b.displayPricePlan=1 ORDER BY `bc`.idCategory ASC, `b`.id ASC

0
 
vidda22Author 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?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
vidda22Author 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that line should check how many other business price plans are there "before" this one ... and hence limit to 4 rows ...
0
 
KalpanCommented:
Sorry couldn't test ...please modify ....hope that should work

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 city c ON b.idCity=c.id WHERE c.id=1 AND
bc.id in (SELECT bpp.idBusinessCategory FROM business_categories bc RIGHT JOIN  business_price_plan bpp ON bc.id=bpp.idBusinessCategory LIMIT 4)
AND b.displayPricePlan=1 ORDER BY `bc`.idCategory ASC, `b`.id ASC
0
 
vidda22Author 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?
0
 
vidda22Author Commented:
@angelIII
Have you got any idea why it comes back with only one item per query?
thanks for help.
0
 
vidda22Author Commented:
if you can guys please recommend me some reading, so I can understand it myself.

Many thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I can only "guess", as I don't see the actual data ...
anyhow, I have to presume that the "limit 4" is to be applied on the  business_price_plan  table, and that the table has an ID column (or some other column to "sort by") .
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now