sql server – joining two database tables in mysql and laravel


I have these two tables.

as_tbl_company_holidays

id year company_id start_date, end_date, description

1 2020 1 2020-01-01 2020-01-01 New Year Holiday

2 2020 1 2020-02-14 2020-02-15 Valentine Holiday

3 2020 2 2020-01-01 2020-01-01 New Year Holiday

as_tbl_employee_holidays

id employee_id company_id year start_date end_date description

1 ASL100 1 2020-01-31 2020-01-31 Casual Holiday

2 ASL200 2 2020-04-01 2020-04-02 Easter Holiday

How can i join these two table to get a holiday data for a particular employee i.e.

id employee_id company_id year start_date end_date description

1 ASL100 1 2020 2020-01-01 2020-01-01 New Year Holiday

2 ASL100 1 2020 2020-02-14 2020-02-15 Valentine Holiday

3 ASL100 1 2020-01-31 2020-01-31 Casual Holiday

id employee_id company_id year start_date end_date description

1 ASL200 2 2020 2 2020-01-01 2020-01-01 New Year Holiday

2 ASL200 2 2020 2 2020-04-01 2020-04-02 Easter Holiday

Meanwhile, I wrote this SQL query and it’s returning duplicate data. How can i combine the data from both tables into one.

select emp.employee_id, comp.company_id, comp.year, comp.start_date, comp.end_date from as_tbl_company_holidays as comp left join as_tbl_employee_holidays as emp on comp.company_id = emp.company_id where emp.employee_id = “ASL100” and comp.company_id = 1