LEFT JOIN is adding rows to my original table

Question

I'm trying to join three 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 number of rows of the COURT_RECORDS table.

Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Aug 18 '22 at 15:52
Unfortunately, no. This question is specifically related to duplicates when joining tables, the example you posted does not involve any joins. — DiamondJoe12, Aug 18 '22 at 15:56
The link I gave was for your goal. It can be done many ways & there are many answers & some involve join. There are many many Q&A for this & there is even a tag. The reason why your 2nd code doesn't work is . [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) Both the goal & the bug are easily found faqs. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. — philipxy, Aug 18 '22 at 23:34
Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect justified by reference to authoritative documentation or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Aug 18 '22 at 23:37
Re (re)search success: Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) — philipxy, Aug 18 '22 at 23:37
Did you [hack into the Västra Götaland Police database to solve a cold case with SQL](https://oracle-wtf.blogspot.com/2012/05/girl-with-ansi-tattoo.html)? — William Robertson, Aug 20 '22 at 10:38

score 1 · Accepted Answer · edited Aug 23 '22 at 19:34

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

score 0 · Answer 2 · answered Aug 18 '22 at 15:27

0

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

answered Aug 18 '22 at 15:27

mmille

54
3

Hi, I edited my question. In my example, I need to join statute from the STATUTE_DESCRIPTION table, not arrests. The solution provided above still results in duplicates. – DiamondJoe12 Aug 18 '22 at 15:53

LEFT JOIN is adding rows to my original table

2 Answers2