MySQL Inner Join with subquery

Question

I have a education table and a resume table. The education table has multiple rows for each resume id.

SELECT * FROM education

id  resume_id board_university  from_date   to_date     qualification_id    percentage  
24  1         Goa Board        2006-03-09   2006-04-09        1         65  
25  1         Goa Board        2008-04-06   2008-05-06        2         58

I am trying to make a search query on resume table. The following is my query

SELECT a.* 
FROM resume a, 
     qualification_master b, 
     users c,  
     (
       SELECT max(to_date) 
       FROM education e 
       WHERE ( e.qualification_id=1 OR e.qualification_id=2)
       GROUP BY e.resume_id
     ) AS d 
INNER JOIN ON a.id=d.resume_id 
WHERE a.uid=c.id
      AND d.qualification_id=b.id 
      AND a.profile_id=26 
      AND a.total_exp_in_yrs >=5 
      AND ((c.state=11 and c.city=331) or a.relocate="y")

But the query is throwing error. Can any one please help?

The result should pickup the qualification_id based on the max(to_date) in the education table.

Answer 1

what you did wrong:

1. In the subquery d, you need to select both resume_id and qualification_id. Additionally, you should alias the aggregated column to something meaningful.

2. You're missing the alias for the INNER JOIN condition.

3. String comparisons in SQL should use single quotes (') instead of double quotes (").

---

SELECT a.* 
FROM resume a
INNER JOIN users c ON a.uid = c.id
INNER JOIN qualification_master b ON d.qualification_id = b.id
INNER JOIN (
    SELECT e.resume_id, MAX(e.to_date) AS max_to_date
    FROM education e 
    WHERE e.qualification_id IN (1, 2)
    GROUP BY e.resume_id
) AS d ON a.id = d.resume_id 
WHERE a.profile_id = 26 
  AND a.total_exp_in_yrs >= 5 
  AND (c.state = 11 AND c.city = 331 OR a.relocate = 'y');

• The subquery d selects the resume_id and the maximum to_date for each resume_id.
• The INNER JOIN conditions are properly aliased.
• String comparisons use single quotes.
• The qualification_id is joined with qualification_master table using INNER JOIN.

Please make sure to adjust the table and column names if they differ in your actual database schema.