CODEWITHSUNDEEP
mysqlleft-joinlimit
Aanshi
Aanshi

Reputation: 93

MySQL : Left join with group by records

I have 2 tables

Job

id | job_num | comp_date
-----------------------------
 1 | J01     | NULL
 2 | J02     | NULL
 3 | J03     | NULL
 4 | J04     | NULL
 5 | J05     | NULL

apt

id | job_id | status | apt_date   | user_id
-----------------------------------------
1  | 1      | 1      | 2018-08-09 | 1
2  | 1      | 1      | 2018-08-09 | 2
3  | 2      | 2      | 2018-08-09 | 1

And i'm trying to get data from 2 tables with left join where limit

Query

SELECT j.* , a.* FROM `job` j 
LEFT JOIN apt a 
ON j.id = a.job_id AND a.status = 1
Group by a.date

Result

id | job_num | comp_date| id | job_id | status | apt_date
---------------------------------------------------------
 2 | J02     | NULL     |NULL|NULL    | NULL   | NULL
 1 | J01     | NULL     | 1  |1       | 1      | 2018-06-05

I need all data from Job table and from 1 record from apt table where status 1

Expected Output

id | job_num | comp_date| id | job_id | status | apt_date
---------------------------------------------------------
 1 | J01     | NULL     | 1  |1       | 1      | 2018-06-05
 2 | J02     | NULL     |NULL|NULL    | NULL   | NULL
 3 | J03     | NULL     |NULL|NULL    | NULL   | NULL
 4 | J04     | NULL     |NULL|NULL    | NULL   | NULL
 5 | J05     | NULL     |NULL|NULL    | NULL   | NULL

How to group by record ?

Upvotes: 1

Views: 402

Answers (1)

Aanshi
Aanshi

Reputation: 93

Finally I found it.

SELECT j.* , a.* FROM `job` j 
LEFT JOIN (SELECT * FROM `apt` WHERE `date` IS NOT NULL AND status =1 GROUP by `date`) as a 
ON j.id = a.job_id

Upvotes: -2

Related Questions