CODEWITHSUNDEEP
mysqlsqlmariadb
BrianV
BrianV

Reputation: 416

All my derived tables have aliases! Why am I getting 'Error 1248: Every derived table must have it's own alias'?

Every time I run this query, I get:

ERROR 1248 (42000): Every derived table must have its own alias

As you can see, we have a parent query which left joins against a derived table created by a subquery.

This subquery in turn selects from a second derived table, and inner joins a third derived table.

All three derived table have proper aliases (n1, n2 and subquery)

The subquery executes as expected when I execute it independently. The issue only occurs when I wrap it in the parent query.

Query:

SELECT DATE_FORMAT(p.date_admitted, '%Y-%m') as month_admitted,
  diagnosis as diagnosis,
  education as education,
  COUNT(*) as total 
FROM patient_discharge_form d 
INNER JOIN survey_data sd ON sd.id = d.id 
LEFT JOIN submission s ON s.id = sd.submission_id 
LEFT JOIN patient p ON p.id = s.patient_id 
LEFT JOIN (
  SELECT n1.id,n1.diagnosis,n2.education
  FROM (
          SELECT id,'Gest Hyp' as diagnosis FROM patient_discharge_form WHERE gestational_hypertension=1
          UNION ALL
          SELECT id,'Pre w/ Sev' FROM patient_discharge_form WHERE preeclampsia_non_severe=1
        ) n1 
  INNER JOIN (
          SELECT id,'Written' as education FROM patient_discharge_form WHERE education LIKE '%written%'
          UNION ALL
          SELECT id,'Verbal' FROM patient_discharge_form WHERE education LIKE '%verbal%'
        ) n2 ON n1.id=n2.id
) subquery ON d.id = subquery.id 
WHERE (s.status = 'complete') 
GROUP BY month_admitted, diagnosis, education

Upvotes: 1

Views: 226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Hmmm . . . I don't see a problem with the table aliases in the query. I wonder if this is exactly the query you are running.

However, I do see a problem with the column aliases. The education column in the select (and perhaps diagnosis as well) is ambiguous. It could come from either d or subquery and perhaps other tables as well.

In general, you should qualify all column names in a query to avoid problems.

Upvotes: 0

Related Questions