CODEWITHSUNDEEP
sqloracle-database
SoConfused
SoConfused

Reputation: 1989

SQL Results for Duplicates - Oracle

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?

Upvotes: 0

Views: 63

Answers (2)

Nikhil
Nikhil

Reputation: 3950

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.

Upvotes: 0

dbusern
dbusern

Reputation: 315

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;

Upvotes: 2

Related Questions