CODEWITHSUNDEEP
mysqlsqllimit
user1490014
user1490014

Reputation: 43

Mysql how limit left join results

I have two tables: songs and groups

i want limit the songs are match the group to 3

i tried this:

SELECT
groups.`IDgroup`,
groups.`name` AS g_name,
songs.`IDsong`,
songs.`name` AS s_name
FROM  `groups` 
LEFT OUTER JOIN songs ON (groups.`IDgroup` = songs.`IDgroup` LIMIT 3)

Upvotes: 2

Views: 337

Answers (5)

Sebas
Sebas

Reputation: 21532

This query will return the last 3 songs for each group:

SELECT
    c.`IDgroup`,
    c.`name` AS g_name,
    s.`IDsong`,
    s.`name` AS s_name
FROM
    groups c 
        JOIN (
            SELECT 
                IF(@C != c.IDgroup, @ROWNUM := 1, @ROWNUM := @ROWNUM +1) AS RN,
                @C := c.IDgroup,
                c.IDgroup,
                s.IDsong,
                s.name
            FROM groups c 
                LEFT JOIN songs s ON c.`IDgroup` = s.`IDgroup`
                CROSS JOIN (SELECT @C := '') t2
            ORDER BY c.IDgroup ASC
        ) s ON c.`IDgroup` = s.`IDgroup`
        JOIN JOIN (
            SELECT IDgroup, MAX(rn) AS mx
            FROM (
                SELECT 
                    IF(@C != c.IDgroup, @ROWNUM := 1, @ROWNUM := @ROWNUM +1) AS rn,
                    @C := c.IDgroup,
                    c.IDgroup
                FROM groups c 
                    LEFT JOIN songs s ON c.`IDgroup` = s.`IDgroup`
                    CROSS JOIN (SELECT @C := '') t2
                ORDER BY c.IDgroup ASC
            ) t
            GROUP BY IDgroup
        ) maxsong ON maxsong.IDgroup = c.IDgroup AND s.rn BETWEEN maxsong.mx-2 AND maxsong.mx
ORDER BY c.IDgroup ASC, s.`name` ASC

Fiddle: http://sqlfiddle.com/#!2/b65c3b/1

Upvotes: 1

sai kiran
sai kiran

Reputation: 38

SELECT
g.`IDgroup`,
g.`name` AS g_name,
s.`IDsong`,
s.`name` AS s_name
FROM  `groups` g 
LEFT OUTER JOIN songs s 
using ('idgroup')
LIMIT 3

Upvotes: 1

amrith
amrith

Reputation: 973

It is generally not a good idea to put a limit on a query that does not explicitly order its results. The reason is that it could return different results over time.

So, you may want to consider adding an

ORDER BY groups.IDgroup, songs.IDsong

to your query (before the LIMIT 3), assuming that this combination is unique.

Upvotes: 1

Joke_Sense10
Joke_Sense10

Reputation: 5402

Put the limit out of parentheses:

SELECT
groups.`IDgroup`,
groups.`name` AS g_name,
songs.`IDsong`,
songs.`name` AS s_name
FROM  `groups` 
LEFT OUTER JOIN songs 
ON groups.`IDgroup` = songs.`IDgroup` 
LIMIT 3

Upvotes: 1

elixenide
elixenide

Reputation: 44831

Take the LIMIT out of the parentheses:

SELECT
    groups.`IDgroup`,
    groups.`name` AS g_name,
    songs.`IDsong`,
    songs.`name` AS s_name
FROM  `groups` 
LEFT OUTER JOIN songs USING (`IDgroup`)
LIMIT 3

Upvotes: 0

Related Questions