Menu
I'm trying to replicate the logic as show here. However, I'm not having any luck when there are joins. Below is a minimised version of my query:
SELECT resources.title, catRel.catRef
FROM resources
LEFT JOIN placesRel ON placesRel.refId = resId
LEFT JOIN catRel ON refId = resId
WHERE ...
In short, I'm getting a list, which contains category Ids and I want to limit the results to have no more than n results from a category, for example, show only two results per catRef:
title catRef
Swizz Gubbinz 1
Runcible Spoons 1
Peter Pan DVD 2
Button Moon 2
Monkey Alan 3
Bilge Pump 3
893
Using Group By with Inner JoinSQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables.
In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
How about using an subquery within your join. I wasn't sure which table refID and resID belonged to but.....
SELECT resources.title, catRel.catRef
FROM resources
LEFT JOIN placesRel ON placesRel.refId = resId
LEFT JOIN catRel as cr1 ON cr1.catRel.primaryKey in (select cr2.primaryKey from catRel as cr2 where cr2.refID = resId Limit 0,2)
WHERE ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
