CODEWITHSUNDEEP
sqlsql-serverinner-join
cool breeze
cool breeze

Reputation: 4811

When I inner join, I get duplicate rows, how to properly write sql

I am trying to create a scenerio where I always seem to get stuck when writing queries.

I want the sql output to simply be a list of users from the users table, no extra columns or duplicated rows.

The tables I have are:

Users
Locations
Location_Types
Location_Types_Zones

The below query is kind of what I am after, but the problem occurs when I start to INNER JOIN with the location_type_zone table. This ends up ruining the result set (in terms of what I want). If I was to remove the location_type_zone inner join and the location_type table that I need to also join to property join the zones table, the format of the output would be correct but the correctness would be off since I am not filtering by the exact location_types 'A', 'B', 'C'.

SELECT u.*
FROM Users u.
    INNER JOIN Locations l
        ON u.location_id = l.location_id
    INNER JOIN location_types lt
        ON l.location_type = lt.location_type_id
    INNER JOIN location_types_zones ltz
        ON lt.location_types_zone_id = ltz.location_types_zone_id
WHERE u.approved = 1
    AND ltz.location_types_zone_code IN ('A', 'B', 'C')

What is the best way to solve this type of sql "issue"?

Updated I updated the fixes as per the comments, sorry about that.

Upvotes: 0

Views: 309

Answers (1)

Andomar
Andomar

Reputation: 238296

A readable approach would be an exists clause:

SELECT  *
FROM    Users u
WHERE   approved = 1
        AND EXISTS
        (
        SELECT  *
        FROM    Locations l
        JOIN    location_types lt
        ON      l.location_type = lt.location_type_id
        JOIN    location_types_zones ltz
        ON      lt.location_types_zone_id = ltz.location_types_zone_id
        WHERE   u.location_id = l.location_id
                AND ltz.location_types_zone_code IN ('A', 'B', 'C')
        )

Or alternatively, as a filtering join like:

SELECT  u.*
FROM    Users u
JOIN    (
        SELECT  DISTINCT l.location_id
        FROM    Locations l
        JOIN    location_types lt
        ON      l.location_type = lt.location_type_id
        JOIN    location_types_zones ltz
        ON      lt.location_types_zone_id = ltz.location_types_zone_id
        WHERE   lt.location_types_zone_code IN ('A', 'B', 'C')
        ) loc
ON      u.location_id = loc.location_id
WHERE   approved = 1

Upvotes: 2

Related Questions