CODEWITHSUNDEEP
mysqljoin

Reputation:

mysql join limit with two tables

i have two tables like this ->

Categories
-----------
id      name
---------
1 -->      a
2    -->   b
3    -->   c



    Messages
    ------------
    id        catid    message
    ---------------------------
    1   -->      1   -->     aa
    2      -->   1   -->     bb
    3        --> 1   -->     cc
    4        --> 2   -->     dd
    5        --> 3    -->    ee
    6        --> 3    -->    ff

    i want to join the tables for get FIRST message from messages table,

i want query result like this ->

-------------------------------
id         name       message
1          a          aa
2          b          dd
3          c          ee

i found a code

select * from categories c, items i
    -> where i.categoryid = c.id
    -> group by c.id;

but there isn't any ORDER BY procedure

Upvotes: 2

Views: 1086

Answers (2)

IgorS
IgorS

Reputation: 163

MySql will always return the first record from 2nd table if you group by the first one.

SELECT a.id, a.name, b.message FROM Categories a, Messages b WHERE b.catid = a.id GROUP BY a.id ORDER BY a.id

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562310

SELECT c.*, i.*
FROM categories c
JOIN items i ON (i.categoryid = c.id)
LEFT OUTER JOIN items i2 ON (i2.categoryid = c.id 
  AND (i.message > i2.message OR i.message = i2.message AND i.id > i2.id))
WHERE i2.categoryid IS NULL
ORDER BY c.id;

This tends to perform better on MySQL, since MySQL does so badly at GROUP BY queries.

Upvotes: 1

Related Questions