MySQL JOIN with LIMIT 1 on joined table

Question

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

User · Answer

Another example with 3 nested tables  1  User 2  UserRoleCompanie 3  Companie  1 user has many UserRoleCompanie  1 UserRoleCompanie has 1 user and 1 Company 1 Companie has many UserRoleCompanie  SELECT  u id as userId   u firstName  u lastName  u email  urc id   urc companieRole  c id as companieId  c name as companieName FROM User as u  JOIN UserRoleCompanie as urc ON u id   urc userId     AND urc id             SELECT urc2 id         FROM UserRoleCompanie urc2          JOIN Companie ON urc2 companieId   Companie id         AND urc2 userId   u id          AND Companie isPersonal   false         order by Companie createdAt DESC                  limit 1            LEFT JOIN Companie as c ON urc companieId   c id   --------------------------- ----------- -------------------- --------------------------- --------------------------- -------------- --------------------------- -------------------    userId                      firstName   lastName             email                       id                          companieRole   companieId                  companieName         --------------------------- ----------- -------------------- --------------------------- --------------------------- -------------- --------------------------- -------------------    cjjt9s9iw037f0748raxmnnde   henry       pierrot              henry gmail com             cjtuflye81dwt0748e4hnkiv0   OWNER          cjtuflye71dws0748r7vtuqmg   leclerc

User · Answer

Replace the tables with yours   SELECT   FROM works w  LEFT JOIN   SELECT photoPath  photoUrl  videoUrl FROM workmedias LIMIT 1  AS wm ON wm idWork   w idWork

User · Answer

I would try something like this   SELECT C           SELECT P id  P title         FROM products as P        WHERE P category id   C id        LIMIT 1  FROM categories C

User · Answer

Accepted answer by  goggin13 looks wrong  Other solutions provided to-date will work  but suffer from the n 1 problem and as such  suffer a performance hit   n 1 problem  If there are 100 categories  then we would have to do 1 select to get the categories  then for each of the 100 categories returned  we would need to do a select to get the products in that category  So 101 SELECT queries would be performed   My alternative solution solves the n 1 problem and consequently should be significantly more performant as only 2 selects are being performed   SELECT     FROM      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     ORDER BY c id ASC  AS a  GROUP BY id

User · Answer

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

User · Answer

What about this   SELECT c id  c title   SELECT id from products AS p                              WHERE c id   p category id                              ORDER BY                                  LIMIT 1     FROM categories AS c

User · Answer

When using postgres you can use the DISTINCT ON syntex to limit the number of columns returned from either table    Here is a sample of the code   SELECT c id  c title  p id AS product id  p title FROM categories AS c JOIN     SELECT DISTINCT ON p1 id  id  p1 title  p1 category id    FROM products p1   p ON  c id   p category id   The trick is not to join directly on the table with multiple occurrences of the id  rather  first create a table with only a single occurrence for each id

User · Answer

Assuming you want product with MIN  imial value in sort column  it would look something like this   SELECT    c id  c title  p id AS product id  p title FROM    categories AS c INNER JOIN     SELECT     p id  p category id  p title   FROM     products AS p   CROSS JOIN       SELECT p category id  MIN sort  AS sort     FROM products     GROUP BY category id     AS sq USING  category id    AS p ON c id   p category id

User · Answer

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 GROUP BY c id   This will return the first data in products  equals limit 1

User · Answer

The With clause would do the trick  Something like this   WITH SELECTION AS  SELECT id FROM products LIMIT 1  SELECT a id  c id  c title FROM selection a JOIN categories c ON  c id   a id

[mysql] MySQL JOIN with LIMIT 1 on joined table

Examples related to mysql

Examples related to join

Examples related to limit