Question 20312 in Mysql Question posted in
The official MySQL documentation can be found here.

sql query: how to filter records on table by applying where condition on a top record from joined table – Mysql

I want to get records from Leads table by applying where condition(e.g when LSH.status=5) should apply on only on top first record(i made LSH.leads_status_id DESC) from table leads_status_history, how to filter the records ?
But currently by using below sql query it filters (i.e where LSH.status=5) it filter out all records with this condition . I want to filter only on latest single record

Here is Expected output explanation

Case1: When LSH.status=5, (because with status=5 it is latest record)

expected Output:

leads_name
---------
Ritaz Texttile

Case 2: When LSH.status=1,
expected Output:

0 record (Because its old record, no matched)

Case 3: When LSH.status=2,
expected Output:

0 record (Because its old record, no matched)

Case 4: When LSH.status=3,
expected Output:

0 record (Because no matched,no records in database)

Query I tried for example for case 1

select L.leads_name
    from leads L LEFT JOIN leads_user_relation LUR ON LUR.leads_id_fk=L.leads_id 
    LEFT JOIN leads_status_history LSH ON LUR.leads_user_relation_id=LSH.leads_user_relation_id_fk 
    WHERE   LUR.leads_id_fk IS NOT NULL 
    AND (LSH.leads_user_relation_id_fk is NOT null AND LSH.status=5)
    AND LUR.user_id_fk =1 GROUP BY L.leads_id ORDER BY LSH.leads_status_id DESC

SQL Fiddle you can try here
http://sqlfiddle.com/#!9/d7e081/4

Table Relations:
enter image description here

Tags:

2

Answers


  1. 0

    Try the following:

    WITH LatestStatus AS (
  SELECT
    LSH.leads_user_relation_id_fk,
    LSH.status,
    ROW_NUMBER() OVER (PARTITION BY LSH.leads_user_relation_id_fk ORDER BY LSH.leads_status_id DESC) AS rn
  FROM
    leads_status_history LSH
)
select  L.leads_name
from leads L 
LEFT JOIN leads_user_relation LUR 
  ON LUR.leads_id_fk=L.leads_id 
LEFT JOIN LatestStatus LSH 
  ON LUR.leads_user_relation_id=LSH.leads_user_relation_id_fk 
WHERE LUR.leads_id_fk IS NOT NULL 
  AND 
  (
      LSH.leads_user_relation_id_fk is NOT null 
      AND LSH.status = 5
      AND LSH.rn = 1
  )
  AND LUR.user_id_fk = 1

    The idea is to first order the records from your history table and get only the first one (with the highest status).

    Login or Signup to reply.
  2. 0

    try this one

    SELECT L.leads_name
FROM leads L 
LEFT JOIN leads_user_relation LUR ON LUR.leads_id_fk=L.leads_id 
LEFT JOIN (
    SELECT status, leads_status_id, leads_user_relation_id_fk
    FROM  leads_status_history 
    ORDER BY leads_status_id DESC limit 1) LSH ON LUR.leads_user_relation_id = LSH.leads_user_relation_id_fk 
WHERE   LUR.leads_id_fk IS NOT NULL 
AND (LSH.leads_user_relation_id_fk is NOT null AND LSH.status=5)
AND LUR.user_id_fk =1 GROUP BY L.leads_id ORDER BY LSH.leads_status_id DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search