英文:
Oracle SQL query, fetching value of occupation in a condition
问题
我感激你在这个问题上的帮助：
我有3个表（CLIENT，OCCUPATIONS，TRS）
我使用以下连接：
- 连接 cif ON TRS.trs_ac_cif = cif.cif_no
- 连接 OCCUPATIONS ON CIF.OCCUPATION = OCCUPATIONS.OCCUPATION_CODE
我需要选择表 OCCUPATIONS 中的名为 BRIEF_DESC 的列，其中 CIF_NO = TO_TRS_AC_CIF
但我不希望这个条件应用于查询的所有部分，我尝试过做一个子查询，但出现错误，显示“单行子查询返回多个值”
谢谢你，
英文:
i appreciate your help on this problem :
i have 3 tables (CLIENT,OCCUPATIONS,TRS)
I USE THE FOLLOWING JOINS:
- JOIN cif ON TRS.trs_ac_cif = cif.cif_no
- JOIN OCCUPATIONS ON CIF.OCCUPATION = OCCUPATIONS.OCCUPATION_CODE
AND I NEED TO SELECT COLUMN CALLED BRIEF_DESC IN TABLE OCCUPATIONS WHERE THE CIF_NO = TO_TRS_AC_CIF
But i don't want the where condition to be applied on all of the query, i've trid to do a sub query but error shown 'singe-row subquery returns more than one value'
Thank You,
i've trid to do a sub query but error shown 'singe-row subquery returns more than one value'
答案1
得分: 1
虽然我们不太清楚以至于无法确定，但基本问题是，如果您使用类似下面所示的"关联子查询"，即子查询必须返回仅一个值。对此的一种非常常见的方法是使用 MAX()，这将强制子查询只产生一个值，例如：
SELECT
cif.CIF_NO
, (
SELECT max(BRIEF_DESC)
FROM OCCUPATIONS
WHERE cif.CIF_NO = TO_TRS_AC_CIF -- 这里是“关联”
) AS BRIEF_DESC
FROM TRS
JOIN cif ON TRS.trs_ac_cif = cif.cif_no
JOIN OCCUPATIONS ON CIF.OCCUPATION = OCCUPATIONS.OCCUPATION_CODE
如果您有更复杂的需求（例如，您希望获得“最近的”值而不是最大值），那么我们需要问题提供足够的细节以便得出答案。
英文:
Although we don't really know enough to be precise, the basic issue is that if you use a "correlated subquery" like the one seen below - that subquery MUST return only one value. A very common approach to this is to use MAX() which will force the subquery to produce only one value e.g.:
SELECT
cif.CIF_NO
, (
SELECT max(BRIEF_DESC)
FROM OCCUPATIONS
WHERE cif.CIF_NO = TO_TRS_AC_CIF -- the "correlation" is here
) AS BRIEF_DESC
FROM TRS
JOIN cif ON TRS.trs_ac_cif = cif.cif_no
JOIN OCCUPATIONS ON CIF.OCCUPATION = OCCUPATIONS.OCCUPATION_CODE
If you have a more complex need (e.g. you would prefer to have "the most recent" value instead of the maximum value) - then we need the question to provide enough details to enable an answer.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论