MySQL 使用多个 ON 条件连接到同一个表？

发布于 2024-12-29 13:03:51 字数 1236 浏览 0 评论 0原文

我仍然是一个 mysql 新手，我广泛查看了之前的问题，试图找到合适的解决方案。

我有两个表 CaseReportsTempImport2011Q3 和 FDADrugsDB，我想进行左连接以将 CaseReportsTempImport2011Q3.drugname 与 FDADrugsDB.ReferenceDrugName 匹配code> 和 FDADrugsDB.DrugName

我的设想是这样的：

SELECT DISTINCT(CaseReportsTempImport2011Q3.DRUGNAME)
, FDADrugsDB_Product.drugname
, FDADrugsDB_Product.ReferenceDrug
, FDADrugsDB_Product.activeingred
FROM CaseReportsTempImport2011Q3
LEFT JOIN FDADrugsDB_Product ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.ReferenceDrug) 
LEFT JOIN FDADrugsDB_Product ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName) 
ORDER BY LENGTH(CaseReportsTempImport2011Q3.DRUGNAME) ASC

但是这不起作用我得到“不是唯一的表/别名：'FDADrugsDB_Product'” - 有帮助吗？

感谢

编辑更好的解决方案请求/重新表述 Per borealids“但是，我不确定这是否是您想要做的 - 连接表两次将产生乘数的结果。我认为您可能想要一个带有 ON ... OR ... 的连接，使得连接条件是两个链接原因的“或”。”

我想知道该怎么做。

解决方案 ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.ReferenceDrug) 或 CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName

感谢 Tom 和 borealid。

原文

Still a mysql newb and I looked extensively through previous questions trying to find an appropriate solution.

I have two tables CaseReportsTempImport2011Q3 and FDADrugsDB, I want to do a left join to match CaseReportsTempImport2011Q3.drugname to FDADrugsDB.ReferenceDrugName and FDADrugsDB.DrugName

What I envisioned was something like:

SELECT DISTINCT(CaseReportsTempImport2011Q3.DRUGNAME)
, FDADrugsDB_Product.drugname
, FDADrugsDB_Product.ReferenceDrug
, FDADrugsDB_Product.activeingred
FROM CaseReportsTempImport2011Q3
LEFT JOIN FDADrugsDB_Product ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.ReferenceDrug) 
LEFT JOIN FDADrugsDB_Product ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName) 
ORDER BY LENGTH(CaseReportsTempImport2011Q3.DRUGNAME) ASC

But that doesn't work I get 'Not unique table/alias: 'FDADrugsDB_Product'' - Any help?

Thanks

EDIT FOR BETTER SOLUTION REQUEST/REPHRASE
Per borealids "However, I'm not sure this is what you want to do - joining the table twice will produce a multiplicative number of results. I think you might have wanted one join with an ON ... OR ..., making the join condition an "or" of the two reasons for linkage."

I would like to know how to do that.

SOLUTION
ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.ReferenceDrug) OR CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName

Thanks tom and borealid.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

热鲨 2025-01-05 13:03:51

为了使您编写的查询有效，您需要为同一个表的两次使用分配两个不同的关系名称。

LEFT JOIN FDADrugsDB_Product FDA_first ON

然后

LEFT JOIN FDADrugsDB_Product FDA_second ON

使用名称 FDA_first 和 FDA_second 来明确引用每组结果。否则，当您说 FDADrugsDB_Product 时，查询引擎无法判断您的意思 - 其中有两个，每个都不同！

但是，我不确定这是否是您想要做的 - 连接表两次将产生乘数的结果。我认为您可能想要一个带有 ON ... OR ... 的连接，使连接条件成为两个链接原因的“或”。

To make the query you wrote valid, you need to assign two different relation names to the two uses of the same table.

LEFT JOIN FDADrugsDB_Product FDA_first ON

and

LEFT JOIN FDADrugsDB_Product FDA_second ON

and then use the names FDA_first and FDA_second to refer to results from each set distinctly. Otherwise the query engine can't tell what you mean when you say FDADrugsDB_Product - there are two of them, each different!

However, I'm not sure this is what you want to do - joining the table twice will produce a multiplicative number of results. I think you might have wanted one join with an ON ... OR ..., making the join condition an "or" of the two reasons for linkage.

回复收藏 0 原文

ヅ她的身影、若隐若现 2025-01-05 13:03:51

您需要为表提供别名，否则 MySql 将不知道您正在谈论的是 FDADrugsDB 表的哪个实例。 IE

FROM CaseReportsTempImport2011Q3 a
LEFT JOIN FDADrugsDB_Product b ON CaseReportsTempImport2011Q3.DRUGNAME LIKE    TRIM(FDADrugsDB_Product.ReferenceDrug) 
LEFT JOIN FDADrugsDB_Product c  ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName)

You need to give the tables alias names, otherwise MySql won't know which instance of the FDADrugsDB table you are talking about. i.e.

FROM CaseReportsTempImport2011Q3 a
LEFT JOIN FDADrugsDB_Product b ON CaseReportsTempImport2011Q3.DRUGNAME LIKE    TRIM(FDADrugsDB_Product.ReferenceDrug) 
LEFT JOIN FDADrugsDB_Product c  ON CaseReportsTempImport2011Q3.DRUGNAME LIKE TRIM(FDADrugsDB_Product.DrugName)

回复收藏 0 原文

南渊 2025-01-05 13:03:51

  ...
FROM
  CaseReportsTempImport2011Q3  i
  LEFT JOIN FDADrugsDB_Product p ON    i.DRUGNAME LIKE TRIM(p.ReferenceDrug) 
                                    OR i.DRUGNAME LIKE TRIM(p.DrugName) 
ORDER BY 
  LENGTH(i.DRUGNAME) ASC

请注意，您可能应该使用 = 而不是 LIKE，除非 ReferenceDrug 和 DrugName 包含匹配模式。

  ...
FROM
  CaseReportsTempImport2011Q3  i
  LEFT JOIN FDADrugsDB_Product p ON    i.DRUGNAME LIKE TRIM(p.ReferenceDrug) 
                                    OR i.DRUGNAME LIKE TRIM(p.DrugName) 
ORDER BY 
  LENGTH(i.DRUGNAME) ASC

Note that you should probably use = instead of LIKE, unless ReferenceDrug and DrugName contain match patterns.

回复收藏 0 原文

~没有更多了~