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?

Answers


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.


Need Your Help

Android - The API key can only be specified once <meta-data> duplicates

android google-maps api-key

I’m trying to add Google map in to may app, so I have taken the API key. Unfortunately when I inserted the API key in manifest.xml through a meta-data tag, it automatically inserted another meta-da...

Python grouping elements in a list in increasing size

python list python-3.x

my_list = [my_list[int((i**2 + i)/2):int((i**2 + 3*i + 3)/2)] for i in range(int((-1 + (1 + 8*len(my_list))**0.5)/2))]