How to combine two tables using INNER JOIN in SQL query?

Data and Databases
1

Hey folks, I’m stuck with a SQL problem. I’ve got two tables:

-- Table 1: employee_records
CREATE TABLE employee_records (
  record_id INT,
  record_date DATETIME,
  employee_id INT,
  document_name VARCHAR(10)
);

-- Table 2: employee_info
CREATE TABLE employee_info (
  employee_id INT,
  employee_name VARCHAR(10)
);

I’m trying to get the document name and employee name for today and yesterday. I tried this:

SELECT document_name 
FROM employee_records 
WHERE '2023-05-15' >= CURRENT_DATE('2023-05-15', INTERVAL 1 DAY);

But I got an error about ‘CURRENT_DATE’. Any ideas how to fix this and use INNER JOIN to get data from both tables? Thanks!

2

yo iris, ClimbingMonkey’s on the right track. heres a tweak:

SELECT er.document_name, ei.employee_name
FROM employee_records er
JOIN employee_info ei ON er.employee_id = ei.employee_id
WHERE er.record_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

this’ll grab wat u need. lemme know if it works!

3

hey iris72! your query looks interesting, but there’s a few things to tweak. have you considered using INNER JOIN like this:

SELECT er.document_name, ei.employee_name
FROM employee_records er
INNER JOIN employee_info ei ON er.employee_id = ei.employee_id
WHERE er.record_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

this should give you what you need. what do you think? any other challenges youre facing with the query?

4

I’ve encountered similar issues before, and here’s a solution that should work for you:

SELECT er.document_name, ei.employee_name
FROM employee_records er
INNER JOIN employee_info ei ON er.employee_id = ei.employee_id
WHERE er.record_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
AND er.record_date <= CURRENT_DATE();

This query uses INNER JOIN to combine the tables based on employee_id. The WHERE clause filters records for today and yesterday using DATE_SUB and CURRENT_DATE functions. This approach is more reliable and efficient than using string comparisons. Let me know if you need any clarification on this solution.