Self Join result weird

[#1]

Hi all,

Kindly please advise accordingly on self join query..

Query 1. Got result returned

select af.source_system_id, af.ar_id, bf.ar_id as ar_id_old, af.eff_dt as eff_dt_new, bf.eff_dt as eff_dt_old
from
gr_my_mpa.ar_x_ar_x_ip_x_pd bf inner join
gr_my_mpa.ar_x_ar_x_ip_x_pd af on bf.ip_id = af.ip_id and bf.sbj_ar_id = af.ar_id and bf.ar_id <> af.ar_id and bf.ar_id < af.ar_id
WHERE bf.ar_x_ar_tp_id = '101102' AND bf.SOURCE_SYSTEM_ID = 'SIBSMY' and bf.REGULATORY_PRODUCT_GROUPING = 'AUTOLOAN' AND bf.INDV = '1'
AND af.SOURCE_SYSTEM_ID = 'SIBSMY' and af.REGULATORY_PRODUCT_GROUPING = 'AUTOLOAN' AND af.INDV = '1'

Query 2. No result returned just only added additional field at select there -> af.unq_id_src_stm

select af.source_system_id, af.ar_id, bf.ar_id as ar_id_old, af.eff_dt as eff_dt_new, bf.eff_dt as eff_dt_old, af.unq_id_src_stm
from
gr_my_mpa.ar_x_ar_x_ip_x_pd bf inner join
gr_my_mpa.ar_x_ar_x_ip_x_pd af on bf.ip_id = af.ip_id and bf.sbj_ar_id = af.ar_id and bf.ar_id <> af.ar_id and bf.ar_id < af.ar_id
WHERE bf.ar_x_ar_tp_id = '101102' AND bf.SOURCE_SYSTEM_ID = 'SIBSMY' and bf.REGULATORY_PRODUCT_GROUPING = 'AUTOLOAN' AND bf.INDV = '1'
AND af.SOURCE_SYSTEM_ID = 'SIBSMY' and af.REGULATORY_PRODUCT_GROUPING = 'AUTOLOAN' AND af.INDV = '1'

If Query 2, using select * then got result returned

or trim all the condition fields also will get result returned 

Thanks & Regards

[#2]

It seems that you are facing a situation where Query 1 returns results, Query 2 (with an additional field) does not return results, but when using SELECT *, results are returned, and when trimming all the condition fields, results are also returned.

This behavior can be attributed to the way you've constructed your queries:

1. Query 1: This query specifies certain conditions and fields, which may match records in your database.

2. Query 2: In Query 2, you've added an additional field (af.unq_id_src_stm) to the SELECT statement. This change in the SELECT clause can affect the results returned. It's possible that the additional field is causing the query not to match any records due to the way the data is structured or the filter conditions.

3. Using SELECT *: When you use SELECT *, it selects all fields in the result set, and it may include fields that are necessary for the join conditions or other aspects of the query. By selecting all fields, you are getting the complete result set.

4. Trimming Condition Fields: If you trim or remove condition fields, it can affect the filter criteria, and as a result, the query may return results that were previously excluded by the conditions.

To resolve the issue in Query 2, you may need to carefully review the additional field you added and ensure it doesn't unintentionally affect the join conditions or filter criteria. Additionally, ensure that the data you are querying contains the values specified in the conditions and the new field. You should also consider whether the additional field is really needed for your analysis. If it's not necessary, you can remove it to get the results you expect