LEFT JOIN is adding rows to my original table-CodePudding

I'm trying to join 3 different tables.

COURT_RECORDS table

STATUTE            ARREST_TRACKING_NO      
976.03             124
943.44(1)(B)       124
946.49(1)(A)       146
946.49(1)(B)       110
940.19(1)          110
940.19(1)          110
940.19(1)          110
940.19(1)          12

ARREST table (arrest tracking numbers will repeat, but a unique arrest tracking number will always have the same arrest date).

ARREST_TRACKING_NO     ARREST_DATE
124                    3-JUL-22
124                    3-JUL-22
124                    3-JUL-22
124                    3-JUL-22
124                    3-JUL-22
124                    3-JUL-22
124                    3-JUL-22
12                     1-AUG-20
110                    8-JUN-19
110                    8-JUN-19
110                    8-JUN-19
110                    8-JUN-19
110                    8-JUN-19
110                    8-JUN-19
110                    8-JUN-19
12                     12-MAR-21

STATUTE_DESCRIPTION table

STATUTE            DESCRIPTION                    STATUTE_CATEGORY    
976.03             Burglary                       1
943.44(1)(B)       Rape                           1
946.49(1)(A)       Homicide                       1
946.49(1)(B)       Drug Possession                1
946.49(1)(D)       Arson                          2
940.19(1)          Theft                          1
940.19(2)          Petty Larceny                  1
940.19(3)          Grand Larceny                  1
940.19(4)          Operating While Intoxicated    1

The left table is COURT_RECORDS. I want to join in the ARREST_DATE field from the ARREST table and the DESCRIPTION from the STATUTE_DESCRIPTION table. I want to retain all records from the COURT_RECORDS table where the STATUTE_CATEGORY is 1

Desired result:

Arrest tracking number 12 dropped because it isn't STATUTE_CATEGORY = 1

Arrest tracking number 146 is retained but since no corresponding arrest tracking number is found in arrest table, the arrest date is NA

 STATUTE            ARREST_TRACKING_NO     DESCRIPTION      STATUTE_CATEGORY   ARREST_DATE 
  976.03             124                   Burglary         1                  3-JUL-22
  943.44(1)(B)       124                   Rape             1                  3-JUL-22
  946.49(1)(A)       146                   Homicide         1                  NA
  946.49(1)(B)       110                   Drug Possession  1                  8-JUN-19
  940.19(1)          110                   Theft            1                  8-JUN-19
  940.19(1)          110                   Theft            1                  8-JUN-19
  940.19(1)          110                   Theft            1                  8-JUN-19

If I join STATUTE_DESCRIPTION table to COURT_RECORDS, everything works fine and the original number of rows in COURT_RECORDS are preserved:

select *
from COURT_RECORDS
LEFT JOIN STATUTE_DESCRIPTION
  ON STATUTE_DESCRIPTION.STATUTE = COURT_RECORDS.STATUTE
WHERE
 (STATUTE_DESCRIPTION.STATUTE_CATEGORY = 1)

But, when I try to join the ARREST table to COURT_RECORDS

select *
from COURT_RECORDS
LEFT JOIN STATUTE_DESCRIPTION
  ON STATUTE_DESCRIPTION.STATUTE = COURT_RECORDS.STATUTE
LEFT JOIN ARREST
  ON ARREST.ARREST_TRACKING_NO = COURT_RECORDS.ARREST_TRACKING_NO
WHERE
 (STATUTE_DESCRIPTION.STATUTE_CATEGORY = 1)

I get many duplicate rows in COURT_RECORDS.

How can I join arrests where it simply just joins the first row for each arrest tracking number matched? I need to preserve the original # of rows of the COURT_RECORDS table? Thanks in advance!

CodePudding user response：

I believe the dups are because you need to also join on statute

LEFT JOIN ARREST
  ON ARREST.ARREST_TRACKING_NO = COURT_RECORDS.ARREST_TRACKING_NO
     AND ARREST.STATUTE_CD = COURT_RECORDS.STATUTE

CodePudding user response：

Your JOIN returns multiple rows because you have duplicates in the ARREST table. It will depend on your version of ORACLE but I believe you can combine OUTER APPLY with FETCH to retrieve the result you are looking for.

SELECT *
FROM COURT_RECORDS CR
LEFT JOIN STATUTE_DESCRIPTION SD ON SD.STATUTE = CR.STATUTE
OUTER APPLY (SELECT *
             FROM ARREST
             WHERE ARREST_TRACKING_NO = CR.ARREST_TRACKING_NO
             FETCH FIRST ROW ONLY) A
WHERE SD.STATUTE_CATEGORY = 1