Bug: Case #003: The Miami Marina Murder. Data Template Not Correct

[PuuGoo]

I saw two table that were named "Hotel Check In" and "Surveillance Records". This problem when we trying compare column "person_id" and "id-unique key of table Hotel Check In" with table "Surveillance Records", We see that seem we not match in 50 values different. After all we will received a value is "NULL". By the end we will check table "Confession" with 50 values different above to have result without 2 table "Hotel check In" and "Surveillance". Please check again Data Template. Thanks you for read this.

[minhoa1111997]

I could solve the test !!!!!!

[digitamo]

Hello 👋
First of all, I'd like to thank you for working on such a delightful project.

Unfortunately, I also ran into some issues with this case, I suspect there's a potential problem with the . I'll try to describe how I got to this conclusion below.

1. To begin with, I like to establish the I'll approach this in reverse order, in other words, I'll start by establishing the solution to the case according to the source code. If you have not solved this case, please be warned SPOILERS AHEAD!!!
   The murder in this case is "Thomas Brown" with the person_id 8

2. With that out of the way, when I run the SQL query below I get no suspicious_activity from the surveillance_records

    SELECT 
        hotel_checkins.*,
        person.name,
        surveillance_records.suspicious_activity
    FROM hotel_checkins
        LEFT JOIN person AS person ON hotel_checkins.person_id = person.id 
        LEFT JOIN surveillance_records ON hotel_checkins.id = surveillance_records.hotel_checkin_id 
    WHERE hotel_checkins.hotel_name LIKE '%Sunset%'
        AND hotel_checkins.check_in_date = 19860813
        AND person.name = 'Thomas Brown';

This also works if you lookup by the person_id omitting the LEFT JOIN with the "person" table

 SELECT 
        hotel_checkins.*,
        person.name,
        surveillance_records.suspicious_activity
    FROM hotel_checkins
        LEFT JOIN person AS person ON hotel_checkins.person_id = person.id 
        LEFT JOIN surveillance_records ON hotel_checkins.id = surveillance_records.hotel_checkin_id 
    WHERE hotel_checkins.hotel_name LIKE '%Sunset%'
        AND hotel_checkins.check_in_date = 19860813
        AND hotel_checkins.person_id = 8;

NOTE: Running the above query also results in duplicate records, which seems odd to me 🤔

3. Looking at the "surveillance_records" to cross-examine the findings from the previous point, it turned out there was a "suspicious_activity" record for this specific person

SELECT * FROM surveillance_records WHERE person_id = 8;