我正在尝试运行以下查询以从另一个表更新一个表。 日期和电子邮件地址,工作和执行过,不过我用从参考表得到SUBJECT_1嵌套查询没有。 我究竟做错了什么?
SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup INNER JOIN PAD_ApplicantLost2000 ON tblSubjectLookup.Subj_Name=PAD_ApplicantLost2000.raw_subj_interest_1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000源表,PAD_ApplicantLost2000,具有包含,例如,“生物学” A“的原始对象”列。 还有一个表,tblSubjectLookup,这对所有科目的代码,所以Subj.Name具有“生物”和“Subj_ClusName”有“B1”,这是需要我的目标表中去。 然而,在目标表中的字段SUBJECT_1不填充。
我究竟做错了什么?
I'm trying to run the following query to update one table from another. The dates and email address work and carry across, but the nested query I'm using to get Subject_1 from a reference table does not. What am I doing wrong?
SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup INNER JOIN PAD_ApplicantLost2000 ON tblSubjectLookup.Subj_Name=PAD_ApplicantLost2000.raw_subj_interest_1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000The origin table, PAD_ApplicantLost2000, has a 'raw subject' column which contains, for example, 'Biology'. There is another table, tblSubjectLookup, which has codes for all subjects, so Subj.Name has 'Biology' and 'Subj_ClusName' has 'B1', which is what needs to go in my target table. However, the Subject_1 field in the target table does not populate.
What am I doing wrong?
最满意答案
为什么不直接使用WHERE ,而不是再次加入表条款。 我还添加了LIMIT 1只是为了确保只返回值1。
SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup WHERE tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1 LIMIT 1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000另外,也许会更好,办法做到这纯粹是直接加入该表。
SELECT FirstRegistered As SignUpdate, Subj_ClusName AS Subject_1, Email_Address FROM PAD_ApplicantLost2000 LEFT JOIN tblSubjectLookup ON tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1Why not just use a WHERE clause instead of joining the table again. I also added LIMIT 1 just to make sure only 1 value is returned.
SELECT FirstRegistered As SignUpdate, (SELECT Subj_ClusName FROM tblSubjectLookup WHERE tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1 LIMIT 1) AS Subject_1, Email_Address FROM PAD_ApplicantLost2000Another, possibly better, way to do it would just be to join the table directly.
SELECT FirstRegistered As SignUpdate, Subj_ClusName AS Subject_1, Email_Address FROM PAD_ApplicantLost2000 LEFT JOIN tblSubjectLookup ON tblSubjectLookup.Subj_Name = PAD_ApplicantLost2000.raw_subj_interest_1更多推荐
发布评论