Distinct Column, but duplicate other columhs

Question

I am trying to modify a PL/SQL query that pulls a distinct list for a ColdFusion dropdown, which in turn runs a query for a filter based on what was chosen in the dropdown. The problem is, the distinct list has multiple entries as there are a couple of entries that map to 2 different short descriptions.

Here is an example of my query:

SELECT DISTINCT O.LONG_DESCR,  O.OPTION_ID,O.SHORT_DESCR 
FROM OPTION         O   
JOIN GROUP      G   ON      o.GROUP_ID = G.GROUP_ID
WHERE O.SHORT_DESCR IN ('A','B','C','D','E','F')
ORDER BY O.LONG_DESCR

I do not need to join to GROUP in this query, but that is what they had in the previous query I need to update. What happens is when this is ran, it shows an output of something similar to this:

Agent 1   1   'A' 
Agent 1   3   'C'
Agent 2   2   'B'
Agent 3   4   'D'
Agent 3   6   'F'
Agent 4   5   'E'

Notice that short description A and C have the same Long Description and short description D and F have the same long description.

The dropdown only looks at long description and outputs

Agent 1
Agent 1
Agent 2
Agent 3
Agent 3
Agent 4

We need the dropdown to just have Agent 1, 2, 3, and 4 and no duplicates but if they choose, for instance, agent 1 then the query will filter out to just anything with Short Description of 'A' and 'C'.

Maybe I should be thinking about how to do it in ColdFusion but I am not too familiar with that, yet. Any help is appreciated!

It's quite likely that the option_id is the most relevent field to the subsequent query. That being the case, you have to ensure that you offer all relevent values to the user for selection. — Dan Bracuk, Jan 18 '16 at 19:58

score 1 · Answer 1 · edited May 23 '17 at 10:27

If I am understanding correctly, you could use string functions to build a csv list of all id's corresponding to each unique description:

SELECT LISTAGG(OPTION_ID, ',') WITHIN GROUP (ORDER BY OPTION_ID) AS OPTION_ID_LIST
      , LONG_DESCR
  FROM YOUR_TABLE
  GROUP BY LONG_DESCR
  ORDER BY LONG_DESCR
 ;

SQLFiddle

Then use the list of id's as the <select> list "value" and the description the "text":

   <select name="optionID">
      <cfoutput query="yourQuery">
         <option value="#OPTION_ID_LIST#">#LONG_DESCR#</option>         
      </cfoutput>
   </select>

When the form is submitted, you will have a list of id's which can be used in a WHERE IN (...) clause:

    WHERE SomeColumnID IN 
         (   
             <cfqueryparam value="#form.optionID#"   
                cfsqltype="cf_sql_integer" 
                list="true">
         )

Depending on the relationship of GROUP_ID here (which is not clear from the question), there may be other options as well. If each description corresponds to a single GROUP_ID, then simply use GROUP_ID as the select list value, and use the group id in your filter instead.

score 0 · Answer 2 · answered Jan 18 '16 at 19:36

0

If you only want to select the distinct long_description, only select the distinct long_description. Trying to select those other fields is resulting in your duplicates.

answered Jan 18 '16 at 19:36

Tim Jasko

1,532
1
8
12

I understand that. But I need the other fields in order to query off of. There is another query that uses the short_descr field to query off of. – user2921015 Jan 18 '16 at 19:49
Use a query of queries (http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html) to pull out the distinct values, or split it into two different queries. Which makes more sense depends on your data. – Tim Jasko Jan 18 '16 at 20:19

Distinct Column, but duplicate other columhs

2 Answers2