CODEWITHSUNDEEP
mysqlsql
dabra904
dabra904

Reputation: 163

MySQL Issue: Inner Join with Subquery returning no results

I have a database with a set of forms with different form edition versions. The below "working" query successfully selects the most recent form edition by the approved usage date of each form edition.

I'd like to join this query "working" query with another table called "forms_types" that returns the full text description of the columns "form_type".

My attempts to do so are not working - I should get 50 results on the "joined" version of the query as well but it's returning no results instead.

Any help or insight would be appreciated in getting my working query to join correctly!

Working (50 results but no JOIN):

SELECT *
FROM forms AS t
WHERE california =
    ( 
    SELECT MAX(california)
    FROM forms
    WHERE `form_number` = t.`form_number`
    AND trucking_coverage= 1
    AND california IS NOT NULL
    AND california <= '2014-01-29'
    AND form_type <> 0
    )
ORDER BY `t`.`form_type` ASC, `t`.`form_number` ASC

Not Working (No results):

SELECT *
FROM forms AS t
INNER JOIN forms_types 
ON 'forms.form_type' = 'forms_types.form_type'
WHERE california =
    ( 
    SELECT MAX(california)
    FROM forms
    WHERE `form_number` = t.`form_number`
    AND trucking_coverage= 1
    AND california IS NOT NULL
    AND california <= '2014-01-29'
    AND form_type <> 0
    )
ORDER BY `t`.`form_type` ASC, `t`.`form_number` ASC

Upvotes: 0

Views: 85

Answers (1)

peterm
peterm

Reputation: 92805

Are you looking for something like this?

SELECT f.*, t.*
  FROM 
( 
  SELECT form_number, MAX(california) california
    FROM forms
   WHERE trucking_coverage = 1
     AND california IS NOT NULL
     AND california <= '2014-01-29'
     AND form_type <> 0
   GROUP BY form_number
) q JOIN forms f
    ON q.form_number = f.form_number
   AND q.california = f.california JOIN forms_types t
    ON f.form_type = t.form_type
 ORDER BY f.form_type, f.form_number

It can also be rewritten this way (by leveraging non-standard MySQL GROUP BY extension)

SELECT *
  FROM
(
  SELECT *
    FROM forms f JOIN forms_types t
      ON f.form_type = t.form_type
   WHERE f.trucking_coverage = 1
     AND f.california IS NOT NULL
     AND f.california <= '2014-01-29'
     AND f.form_type <> 0
   ORDER BY california DESC
) q
 GROUP BY f.form_number
 ORDER BY f.form_type, f.form_number

Upvotes: 1

Related Questions