如何在第一个查询中创建已经LEFT JOIN的子查询

问题描述：

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.

美好的一天我想问一下我的SQL命令是否有什么问题？我得到的错误是＃1054 - '字段列表'中的未知列'imaster_tbl.id' code> p>

我认为这是因为我把't1' code>在FROM关键字之后的 imaster_tbl code> p>

你可以帮我解决这个问题..这是我的SQL命令： p >


  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（r  evision）FROM imaster_tbl t2 WHERE t2.drawing_part_number = t1.drawing_part_number）
  code>  pre> 
 
 但这个适用于我，没有LEFT JOIN。 p> 
 
  
  select * from table t1 where revision =（从表t2中选择max（revision），其中t2.filename = t1.filename）
  code>  pre> 
 
 谢谢 很多。 p> 
  div>

答

The error states that the server cant find a specific field. It's a bit difficult to say if the field really exists, since you do not post your table definitions. But asuming it does exist you only have to replace imaster_tbl with t1 in you select part of the query. When you are writing "imaster_tbl t1" in the FROM part you are creating an alias of that table, and need to use the alias to reference fields in that table. Alias are usefull in e.g. when you want easier readability of the code, when creating temporary tables or when running the same table more than once, and need to identify how they connect.

I've updated your SQL below

SELECT
  t1.id,
  t1.die_name,
  t1.part_name,
  t1.drawing_number,
  t1.drawing_part_number,
  t1.sub_letter,
  t1.specs,
  t1.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
    master_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
  )

答

In your FROM cluase, you have defined alias t1 for imaster_tbl as FROM imaster_tbl t1 and in the query you are using imaster_tbl to refer to this table/view. You should use the alias name once you declare the alias.

如何在第一个查询中创建已经LEFT JOIN的子查询

相关推荐