XantorAmnobius XantorAmnobius - 2 months ago 5
SQL Question

SQL Query returns more

I'm having a bit of a problem with a SQL Query that returns too many results. I'm fairly new to SQL so please bear with me.

Please see the following:

Table Structures

The Query that I use looks like:

SELECT TABLE_B.*
FROM
TABLE_A
JOIN
TABLE_B
ON
TABLE_A.COMMON_ID=TABLE_B.COMMON_ID
AND TABLE_A.SEQ_3C=TABLE_B.SEQ_3C
JOIN
TABLE_C
ON
TABLE_A.COMMON_ID=TABLE_C.EMPLID
WHERE
TABLE_B.ITEM_STATUS<>'C'
and TABLE_A.CHECKLIST_STATUS='I'
and TABLE_A.ADMIN_FUNCTION='ADMA'
and TABLE_A.CHECKLIST_CD='APPL'
and TABLE_A.COMMON_ID = '123456789'
and TABLE_C.ADMIT_TERM='2171'
and TABLE_C.INSTITUTION='SOMEWHERE'


I just want the results from Table_B and not what it's giving me.
Please explain this to me as I have spent 3 days on it non-stop.

What am I missing?

Answer

You want data from TABLE_B? Then select from it only and have the conditions on the other tables in your where clause.

The inner joins on the other tables serve as existence tests, I assume? Don't do that. You'd only multiply your records, just as you are doing now, only to have to dismiss duplicates later. That can cause bad performance on large tables and errors in more complicated queries. Use EXISTS or IN instead.

select *
from table_b
where item_status <> 'C'
and (common_id, seq_3c) in
(
  select common_id, seq_3c 
  from table_a
  where checklist_status = 'I'
    and admin_function = 'ADMA'
    and checklist_cd = 'APPL'
)
and common_id in
(
  select EMPLID
  from table_c
  where admit_term = '2171'
    and institution = 'SOMEWHERE'
);
Comments