CODEWITHSUNDEEP
sqldatabasesqlitejoinleft-join
Fred_Alb
Fred_Alb

Reputation: 174

Left join tables in sqlite ordered by desc limit

I want to left join two tables in sqlite. Short summary:

I have a table named "_Menu", one of the fields in this table is named "menu_id" with unique numbers.

Another table is called "_Approvals". This table has a history of which items has been "approved" or "unapproved". This table also has a field named "menu_id".

I want to get the lowest row (if there is any) from "_Approvals" for a given menu_id and join the two tables.

What I have so far is:

SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url, a.status, a.auth
FROM _Menu AS m
LEFT JOIN (
SELECT * FROM _Approvals ORDER BY _Approvals.approval_id DESC LIMIT 1) as a
ON a.menu_id = m.menu_id
GROUP BY m.menu_id
ORDER BY m.menu_id

My problem is only the absolute last row in "_Approvals" gets joined. I.e I only know the status of the last item approved/unapproved.

Any help is greatly appreciated!

Upvotes: 0

Views: 66

Answers (2)

forpas
forpas

Reputation: 164099

You want the lowest row (if there is any) from "_Approvals" for a given menu_id and not the the lowest row from "_Approvals" which is what your code does.
One way to do what you need is by using NOT EXISTS in the subquery that you join: 

SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url, a.status, a.auth
FROM _Menu AS m
LEFT JOIN (
  SELECT * FROM _Approvals t
  WHERE NOT EXISTS (
    SELECT 1 FROM _Approvals
    WHERE menu_id = t.menu_id AND approval_id > t.approval_id
  )
) AS a
ON a.menu_id = m.menu_id
GROUP BY m.menu_id
ORDER BY m.menu_id

Another way with a CTE:

WITH cte AS (
  SELECT t.* FROM _Approvals t 
  INNER JOIN (
    SELECT menu_id, MAX(approval_id) 
    FROM _Approvals
    GROUP BY menu_id
  ) g
  ON g.menu_id = t.menu_id
)
SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url, a.status, a.auth
FROM _Menu AS m LEFT JOIN cte AS a
ON a.menu_id = m.menu_id
GROUP BY m.menu_id
ORDER BY m.menu_id

Or with window function ROW_NUMBER() if your version of SQLite is 3.25.0+:

WITH cte AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY menu_id ORDER BY approval_id DESC) rn
  FROM _Approvals 
)
SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url, a.status, a.auth
FROM _Menu AS m LEFT JOIN (
  SELECT * FROM cte
  WHERE rn = 1
) AS a
ON a.menu_id = m.menu_id
GROUP BY m.menu_id
ORDER BY m.menu_id

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would recommend writing this using row_number():

SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url,
       a.status, a.auth
FROM _Menu m LEFT JOIN
     (SELECT a.*,
             ROW_NUMBER() OVER (PARTITION BY a.menu_id ORDER BY a.approval_id DESC) as seqnum
      FROM _Approvals a
     ) a
     ON a.menu_id = m.menu_id AND a.seqnum = 1
ORDER BY m.menu_id;

No aggregation should be needed for the query, assuming that menu_id is the primary key in _Menu.

If you are using an old version of SQLite that doesn't support window functions, then there are several options. Probably the simplest is:

SELECT m.menu_id, m.p_id AS parent_id, m.name, m.url,
       a.status, a.auth
FROM _Menu m LEFT JOIN
     _Approvals a
     ON a.menu_id = m.menu_id LEFT JOIN
     (SELECT a.menu_id, MAX(a.approval_id) as max_approval_id
      FROM _Approvals a
      GROUP BY a.menu_id
     ) aa
     ON aa.menu_id = a.menu_id AND
        aa.max_approval_id = a.approval_id
ORDER BY m.menu_id;

Upvotes: 1

Related Questions