我想知道你是否可以提供帮助 - 我已经创建了一个SQL查询(SQLServer),虽然有些结果没有显示,我认为它是由于某些连接上的NULL结果。 例如,我知道一些连接将使用NULL结果,因为100.resp_unit_no可能是NULL以及100.employee_no。
到目前为止的查询是:
select 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111, 108_JOB_DETAILS 108, 058_RESPONSIBILITY_UNITS 058, 100_ACTIVITIES 100, 105_ACTIVITY_TYPES 105, 210_EMPLOYEES 210, 117_STATUS_DESCRIPTIONS 117 where 111.JOB_NO=108.JOB_NO AND 111.JOB_NO=100.JOB_NO AND 100.RESP_UNIT_NO=058.RESP_UNIT_NO AND 111.JOB_TYPE=105.ACTIVITY_TYPE AND 210.EMPLOYEE_NO=100.EMPLOYEE_NO AND 117.STATUS=100.ACTIVITY_STATUS order by IRU, Employee, EXPECTED_START;我想我需要一个正确的外连接,但我不能让结构正确...
任何帮助将不胜感激!
I'm wondering if you can help - I've got an SQL Query (SQLServer) which i've created, although some results are not being shown and i think its due to NULL results on some joins. For example, i know some of the joins will be using NULL results as 100.resp_unit_no may well be NULL aswell as 100.employee_no.
The query so far is:
select 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111, 108_JOB_DETAILS 108, 058_RESPONSIBILITY_UNITS 058, 100_ACTIVITIES 100, 105_ACTIVITY_TYPES 105, 210_EMPLOYEES 210, 117_STATUS_DESCRIPTIONS 117 where 111.JOB_NO=108.JOB_NO AND 111.JOB_NO=100.JOB_NO AND 100.RESP_UNIT_NO=058.RESP_UNIT_NO AND 111.JOB_TYPE=105.ACTIVITY_TYPE AND 210.EMPLOYEE_NO=100.EMPLOYEE_NO AND 117.STATUS=100.ACTIVITY_STATUS order by IRU, Employee, EXPECTED_START;I think i need a right outer join, but i cant quite get the structure correct...
Any help would be greatly appreciated!
最满意答案
如果您更改查询以使用ANSI-92显式连接语法而不是您正在使用的ANSI-89语法(正如名称建议超过20年的日期),那么您的查询将变得更容易操作:
SELECT 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111 INNER JOIN 108_JOB_DETAILS 108 ON 111.JOB_NO = 108.JOB_NO INNER JOIN 100_ACTIVITIES 100 ON 111.JOB_NO = 100.JOB_NO LEFT JOIN 058_RESPONSIBILITY_UNITS 058 100.RESP_UNIT_NO = 058.RESP_UNIT_NO LEFT JOIN 105_ACTIVITY_TYPES 105 ON 111.JOB_TYPE = 105.ACTIVITY_TYPE LEFT JOIN 210_EMPLOYEES 210 ON 210.EMPLOYEE_NO = 100.EMPLOYEE_NO LEFT JOIN 117_STATUS_DESCRIPTIONS 117 ON 117.STATUS = 100.ACTIVITY_STATUS ORDER BY IRU, Employee, EXPECTED_START;现在,在不知道哪些列可能为null的情况下,我不知道哪个连接要从INNER更改为LEFT ,所以我只更改了其中的一些,但是您应该能够根据需要更改一些。
Aaron Bertrand写了一篇很好的文章 ,更有理由转向更新的连接语法。
If you change your query to use ANSI-92 explicit join syntax rather than the ANSI-89 syntax that you are using (which is, as the names suggest over 20 years out of dates) then you query will become much easier to manipulate:
SELECT 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111 INNER JOIN 108_JOB_DETAILS 108 ON 111.JOB_NO = 108.JOB_NO INNER JOIN 100_ACTIVITIES 100 ON 111.JOB_NO = 100.JOB_NO LEFT JOIN 058_RESPONSIBILITY_UNITS 058 100.RESP_UNIT_NO = 058.RESP_UNIT_NO LEFT JOIN 105_ACTIVITY_TYPES 105 ON 111.JOB_TYPE = 105.ACTIVITY_TYPE LEFT JOIN 210_EMPLOYEES 210 ON 210.EMPLOYEE_NO = 100.EMPLOYEE_NO LEFT JOIN 117_STATUS_DESCRIPTIONS 117 ON 117.STATUS = 100.ACTIVITY_STATUS ORDER BY IRU, Employee, EXPECTED_START;Now, without knowing what columns may be null, I don't know which joins to change from INNER to LEFT, so I have only changed a few of them, but you should be able to change a few more as needed.
Aaron Bertrand has written a good article, with even more reasons to switch to the newer join syntax.
更多推荐
发布评论