Menu
I want to join two tables, but only get 1 record of table2 per record on table1
For example:
SELECT c.id, c.title, p.id AS product_id, p.title FROM categories AS c JOIN products AS p ON c.id = p.category_id This would get me all records in products, which is not what I want. I want 1 [the first] product per category (I have a sort column in the products field).
How do I go about doing that?
702
There's no limit on the number of joins, otherwise it would throw an error almost immediately. They are all Table Values Functions.
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group). This is a groupwise-maximum type query, which is not trivial to do in SQL.
I like more another approach described in a similar question: https://stackoverflow.com/a/11885521/2215679
This approach is better especially in case if you need to show more than one field in SELECT. To avoid Error Code: 1241. Operand should contain 1 column(s) or double sub-select for each column.
For your situation the Query should looks like:
SELECT c.id, c.title, p.id AS product_id, p.title AS product_title FROM categories AS c JOIN products AS p ON p.id = ( --- the PRIMARY KEY SELECT p1.id FROM products AS p1 WHERE c.id=p1.category_id ORDER BY p1.id LIMIT 1 ) 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
