Mysql Join with previous record

join;MySQL

Pardon for being naive. This is a simple simple question but i am unable to figure out how to query this

PhpmyAdmin pic

Query :

I have 2 tables

kknagar_data & kknagar_rates

I am trying to query out in such a way that

  1. get current record from kknagar_data – i was able to achieve this
  2. get kk_egg,kk_cash of previous record from current in kknagar_data
  3. get rates from kknagar_rates by joining kk_rate_id in kk_nagar_rates & kk_nagar_data – I was able to achieve this

What i am trying to achieve

I want all the above 3 points in a single query.

I was able to do point 1 & 3 and i queried like this

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

but i am unable add point 2 to the above query so that i get all the data in a single shot

What i tried :

I tried with Unions but i get error because the columns doesnt match

What i am using now :

Currently i use 2 different queries to achieve the above

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

and

Select kk_egg,kk_cash FROM kknagar_data WHERE kk_data_id < 3 ORDER BY kk_data_id DESC LIMIT 1

It gives me the result but i am trying to change this to make it more efficient. Another reason is when i json out the values, i find it difficult to decode multiple objects.

Goal :

Get current record with 2 fields of previous record and join with kk_nagar_rates & kk_nagar_data using rate_id

Any guindance will be much helpful.

Thanks in Advance

Best Answer

You can use two nearly identical subqueries in your original SELECT:

SELECT 
  kk_data_id,
  kk_date,
  kk_egg,
  kk_cash,
  kk_in,
  kk_out,
  kk_expenditure,
  (  SELECT kk_egg              -- You get kk_egg from the "previous" row
       FROM kknagar_data k2 
      WHERE k2.kk_data_id < k1.kk_data_id 
   ORDER BY kk_data_id DESC
      LIMIT 1
  ) AS prev_kk_egg,
  (  SELECT kk_cash              -- You get kk_cash from the "previous" row
       FROM kknagar_data k2 
      WHERE k2.kk_data_id < k1.kk_data_id 
   ORDER BY kk_data_id DESC 
      LIMIT 1
  ) AS prev_kk_cash
FROM 
  kknagar_data k1
  INNER JOIN kknagar_rates kr on kr.kk_rate_id = k1.kk_rate_id ;

Having two subqueries looking for two different columns on the same row looks like a waste of time, but it basically mimics what you're doing right now and it works.

You can check it at SQLFiddle.

Note on aliases

You have one table (kknagar_data) that is accessed for three different purposes. First you use it to get the list of "normal records". That's when this table is used as kknagar_data k1 (which can also be written as kknagar_data AS k1). The k1 is just an alias for the table, within that specific context.

Then, in your subqueries, you also refer to the same table, but for a different purpose. In that case, I've used a different alias (FROM kknagar_data **k2**), so that I can refer to the same table with two different contexts. The condition k2.kk_data_id < k1.kk_data_id means: look at the same table (k2 and k1 are actually the same table), but right now (k2) choose the ids that are less than the ones for the "normal records" (k1).

Both subqueries use k2 as an alias because I just copied and pasted the code, and changed the column name. That's possible because the symbol k2 is local to each subquery, and cannot be used outside of its context. That's the same as using the same variable name in two adjacent blocks of a program written in, let's say, Java. It is not advisable to use the same alias when one block contains the other, because you would have, at least, ambiguities.

Aliases are needed when you refer more than once to the same table. For instance, when you have self-referencing tables (i.e.: a table persons where you have a parent-child relationship), or when you want to access "a record of the same table somehow related to the one you already have", such as in your case. Sometimes it is just used for simplicity (k1 is shorter than kknagar_data).

Window functions as a (better) alternative

Databases allowing for WINDOW functions would let you to use the lag() function to have the same effect, in a more efficient manner. Check Introduction to LEAD and LAG for Microsoft SQL Server. You can achieve the same with PostgreSQL, or also with the latest versions of MariaDB. MySQL 8.0 is going to have window functions as well.

Related Question