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:
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?
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' );
SELECT DISTINCT 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'
This should be easy to understand without looking at all your tables and output.
Suppose you join two tables, A and B, on a column id. You only want the columns from table B, and in table B the `id' column is a unique identifier.
Even so, if in table A an id (the same id) appears five times, the join will have five rows for that id. Then you just select the columns from table B, so it will look like you got the same row five different times.
Perhaps you don't really need a join? What is your underlying problem you are trying to solve?
It's hard to answer this question without more information about why you're executing these joins. I can explain why you're getting the results you're getting, and hopefully that will allow you to solve the problem yourself.
You start, in your FROM clause, with table A. You join this table with table B on matching COMMON_ID, which, based on the tables you provide, returns three matches for the one record you have in table A. This increases your result set size to three records. Next, you join these three records with table C, on matching ID. Because all ID's are, in fact, identical, this returns nine matches for every record in your current result set: you now have 9 x 3 = 27 records in your result set.
Finally, the WHERE clause comes into effect. This clause excludes 6 out of 9 records in table C, so you have 3 of those records left. Your final result set is therefore 1 (table A) x 3 (table B) x 3 (table C) = 9 records.