Good Day I would like t ask if what seem to be the problem with my SQL command??
the error I've got was #1054 - Unknown column 'imaster_tbl.id' in 'field list'
i think it is because I put 't1'
after imaster_tbl after the FROM keyword
can you please help me with this problem.. here's my SQL Command:
SELECT imaster_tbl.id, imaster_tbl.die_name, imaster_tbl.part_name,
imaster_tbl.drawing_number, imaster_tbl.drawing_part_number,
imaster_tbl.sub_letter,imaster_tbl.specs, imaster_tbl.file_path,
idrawing_type_tbl.drawing_type, idie_type_tbl.die_type, irevision_tbl.revision,
irelay_type_tbl.relay_type FROM imaster_tbl t1
LEFT JOIN idrawing_type_tbl ON imaster_tbl.drawing_type_id=idrawing_type_tbl.drawing_type_id
LEFT JOIN idie_type_tbl ON imaster_tbl.die_type_id = idie_type_tbl.die_type_id
LEFT JOIN irelay_type_tbl ON imaster_tbl.relay_type_id=irelay_type_tbl.relay_type_id
LEFT JOIN irevision_tbl ON imaster_tbl.revision_id = irevision_tbl.revision_id
WHERE revision = (SELECT MAX(revision) FROM imaster_tbl t2 WHERE t2.drawing_part_number = t1.drawing_part_number)
but this one works for me, without LEFT JOIN.
select * from table t1 where revision = (select max(revision) from table t2 where t2.filename = t1.filename)
thanks a lot.