SQL Results for Duplicates - Oracle

Question

SO community, here's my SQL query which runs with no error:

SELECT DISTINCT
    (L.LOCATION) AS "LOC",
    C.CITY_NAME AS "CIT",
    H.STREET_NAME AS "STR",
    U.APT_NAME AS "APT",
    U.NUM AS "NUM",
    L.DESCRIPTION AS "DES",
    H.POP AS "POP",
    S.STATE_CODE AS "STA",
    H.SITE_NAME AS "SIT",
    U.COUNTY AS "COU",
    L.DESCRIPTION AS "PRO"
FROM
    LOCATIONS L
    LEFT OUTER JOIN CGZIP9 Z
    ON L.L_ZIPCODE = Z.ZIPCODE
    LEFT OUTER JOIN STREET_V H
    ON L.LOCATION = H.LOCATION
    LEFT OUTER JOIN CGZIPCITY Y
    ON Z.CITYID = Y.CITYID
    LEFT OUTER JOIN CGCITY C
    ON Y.CITYID = C.CITYID
    LEFT OUTER JOIN CGSTATE S
    ON C.STATEFIPS = S.STATEFIPS
    LEFT OUTER JOIN UNIT U
    ON L.UNITID = U.UNIT_ID
WHERE
    L.LOCATION IN ('1', '2', '3', '4', '5', '1', '2', '4')
    ORDER BY L.LOCATION ASC;

I'm trying to retrieve records for all values in my IN (even the duplicates such as '1', '2', & '4')

Here's my output when I run the above code:

=============================================
|LOC|CIT|STR|APT|NUM|DES|POP|STA|SIT|COU|PRO|
=============================================
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |
| 3 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X | 
| 5 | X | X | X | X | X | X | X | X | X | X |  
=============================================

Here's my desired output:

=============================================
|LOC|CIT|STR|APT|NUM|DES|POP|STA|SIT|COU|PRO|
=============================================
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |
| 3 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X | 
| 5 | X | X | X | X | X | X | X | X | X | X |  
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X |  
=============================================

Any suggestions as to how I can accomplish this?

drop the 'distinct' from your select? And having a value twice in your in statement has won't make any difference on what rows are returned — Andrew
– Andrew, Commented Sep 6, 2018 at 17:42
Maybe if you remove the DISTINCT, the query should work, showing you the records in the same way you need. — Hackerman
– Hackerman, Commented Sep 6, 2018 at 17:43
x in (1,1) means the same thing as (x = 1 or x = 1), so you would just get rows where x = 1. It's like requesting "all pets that are cats or cats". You don't get all the cats twice. — William Robertson
– William Robertson, Commented Sep 6, 2018 at 21:53

dbusern · Accepted Answer · 2018-09-06 18:17:30Z

2

Please try out the UNION ALL approach which does not filter out duplicates, as follows:

SELECT DISTINCT (L.LOCATION)  AS "LOC",
                C.CITY_NAME   AS "CIT",
                H.STREET_NAME AS "STR",
                U.APT_NAME    AS "APT",
                U.NUM         AS "NUM",
                L.DESCRIPTION AS "DES",
                H.POP         AS "POP",
                S.STATE_CODE  AS "STA",
                H.SITE_NAME   AS "SIT",
                U.COUNTY      AS "COU",
                L.DESCRIPTION AS "PRO"
  FROM LOCATIONS L
       LEFT OUTER JOIN CGZIP9 Z ON L.L_ZIPCODE = Z.ZIPCODE
       LEFT OUTER JOIN STREET_V H ON L.LOCATION = H.LOCATION
       LEFT OUTER JOIN CGZIPCITY Y ON Z.CITYID = Y.CITYID
       LEFT OUTER JOIN CGCITY C ON Y.CITYID = C.CITYID
       LEFT OUTER JOIN CGSTATE S ON C.STATEFIPS = S.STATEFIPS
       LEFT OUTER JOIN UNIT U ON L.UNITID = U.UNIT_ID
 WHERE L.LOCATION IN ('1',
                      '2',
                      '3',
                      '4',
                      '5')
UNION ALL
SELECT DISTINCT (L.LOCATION)  AS "LOC",
                C.CITY_NAME   AS "CIT",
                H.STREET_NAME AS "STR",
                U.APT_NAME    AS "APT",
                U.NUM         AS "NUM",
                L.DESCRIPTION AS "DES",
                H.POP         AS "POP",
                S.STATE_CODE  AS "STA",
                H.SITE_NAME   AS "SIT",
                U.COUNTY      AS "COU",
                L.DESCRIPTION AS "PRO"
  FROM LOCATIONS L
       LEFT OUTER JOIN CGZIP9 Z ON L.L_ZIPCODE = Z.ZIPCODE
       LEFT OUTER JOIN STREET_V H ON L.LOCATION = H.LOCATION
       LEFT OUTER JOIN CGZIPCITY Y ON Z.CITYID = Y.CITYID
       LEFT OUTER JOIN CGCITY C ON Y.CITYID = C.CITYID
       LEFT OUTER JOIN CGSTATE S ON C.STATEFIPS = S.STATEFIPS
       LEFT OUTER JOIN UNIT U ON L.UNITID = U.UNIT_ID
 WHERE L.LOCATION IN ('1', '2', '4')
ORDER BY L.LOCATION ASC;

answered Sep 6, 2018 at 18:17

dbusern

3154 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SoConfused Over a year ago

is there a way for the query to determine the duplicates instead of me adding them to a 2nd WHERE?

dbusern Over a year ago

Is it your requirement that you must display duplicates if they are present? In that case just remove the keyword ** DISTINCT ** and verify , as others have suggested. By default in absence of DISTINCT and GROUP BY Oracle query will display duplicates as duplicates if they are present. If DISTINCT is there then it will remove the duplicate.

Nikhil · Accepted Answer · 2018-09-11 06:59:14Z

this will work:

SELECT
    L.LOCATION AS "LOC",
    C.CITY_NAME AS "CIT",
    H.STREET_NAME AS "STR",
    U.APT_NAME AS "APT",
    U.NUM AS "NUM",
    L.DESCRIPTION AS "DES",
    H.POP AS "POP",
    S.STATE_CODE AS "STA",
    H.SITE_NAME AS "SIT",
    U.COUNTY AS "COU",
    L.DESCRIPTION AS "PRO"
FROM
    LOCATIONS L
    LEFT OUTER JOIN CGZIP9 Z
    ON L.L_ZIPCODE = Z.ZIPCODE
    LEFT OUTER JOIN STREET_V H
    ON L.LOCATION = H.LOCATION
    LEFT OUTER JOIN CGZIPCITY Y
    ON Z.CITYID = Y.CITYID
    LEFT OUTER JOIN CGCITY C
    ON Y.CITYID = C.CITYID
    LEFT OUTER JOIN CGSTATE S
    ON C.STATEFIPS = S.STATEFIPS
    LEFT OUTER JOIN UNIT U
    ON L.UNITID = U.UNIT_ID
WHERE
    L.LOCATION IN ('1', '2', '3', '4', '5')
UNION ALL
SELECT 
    L.LOCATION AS "LOC",
    C.CITY_NAME AS "CIT",
    H.STREET_NAME AS "STR",
    U.APT_NAME AS "APT",
    U.NUM AS "NUM",
    L.DESCRIPTION AS "DES",
    H.POP AS "POP",
    S.STATE_CODE AS "STA",
    H.SITE_NAME AS "SIT",
    U.COUNTY AS "COU",
    L.DESCRIPTION AS "PRO"
FROM
    LOCATIONS L
    LEFT OUTER JOIN CGZIP9 Z
    ON L.L_ZIPCODE = Z.ZIPCODE
    LEFT OUTER JOIN STREET_V H
    ON L.LOCATION = H.LOCATION
    LEFT OUTER JOIN CGZIPCITY Y
    ON Z.CITYID = Y.CITYID
    LEFT OUTER JOIN CGCITY C
    ON Y.CITYID = C.CITYID
    LEFT OUTER JOIN CGSTATE S
    ON C.STATEFIPS = S.STATEFIPS
    LEFT OUTER JOIN UNIT U
    ON L.UNITID = U.UNIT_ID
WHERE
    L.LOCATION IN ('1', '2', '4');

remove the order by and try union all.

Collectives™ on Stack Overflow

SQL Results for Duplicates - Oracle

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related