Multiple ''Distinct'' SQL syntax needed!

  • I'm attempting to load several comboboxes (C# .NET) from an SQL 2000 data table (Reports). Now the problem is each combobox loads everything, even nulls that I don't need.

    Simpy coding a 'Distinct' in the SQL will eliminate the nulls, however I have more than one column to be selected as 'Distinct'.. The following code is an example of what I need. It obviously does not work though.

    Select distinct col_1, distinct col_2, distinct col_3 from table_1

  • I think you'll need to make a single statement per combo and select only the data you need.

  • I attempted to insert the data into another table like so, but the results are not desirable as shown below..

     

    TRUNCATE TABLE ReportsTemp

    insert into ReportsTemp (col_1) (select distinct col_1 from Reports where col_1 is not null)

    insert into ReportsTemp (col_2) (select distinct col_2 from Reports where col_2 is not null)

    insert into ReportsTemp (col_3) (select distinct col_3 from Reports where col_3 is not null)

     

    Results:

    col_1      col_2     col_3

    data1      null        null

    data1      null        null

    data1      null        null

    null         data2     null

    null         data2     null

    null         null        data3         

     

     

  • Or, if the combo boxes need to relate to each, you may need to re-write your query with either CASE statements or INNER JOINs on Subselects...  

     

    SELECT DISTINCT col_1, col_2, col_3

    FROM table_1

    WHERE 1 = CASE

                          WHEN col_1 IS NULL

                           THEN 0

                           ELSE 1

                          WHEN coL_2 IS NULL

                           THEN 0

                           ELSE 1 ...

                   END

     

    OR

    SELECT DISTINCT col_1, Two.col_1, ...

    FROM table_1

       INNER JOIN( SELECT DISTINCT col_2 FROM table_1 WHERE col_2 IS NOT NULL) Two

           ON( table_1.[ID] = Two.[ID]) ..... [ not sure what field you will need to JOIN upon ]

    WHERE col_1 IS NOT NULL 

     

    I wasn't born stupid - I had to study.

  • This was posted while I was writing my first response. 

    You will not want to INSERT col_2, col_3, etc.  You will want to UPDATE your #TempTable based upon some key...

    I wasn't born stupid - I had to study.

  • I like both responces. For now I would like to try the Update. I do have a Primary col_Pri, but not sure how I would make this work. Can you show syntax......

    Results:

    col_Pri     col_1      col_2     col_3

    1            data1      null        null

    2            data1      null        null

    3            data1      null        null

    4            null         data2     null

    5            null         data2     null

    6            null         null        data3         

  • Can you tell us again why you need to do this?????????

  •  "I'm attempting to load several comboboxes (C# .NET) from an SQL 2000 data table (Reports). Now the problem is each combobox loads everything, even nulls that I don't need. "

  • Why can't the combo boxes be loaded independently? 

     

     

    I wasn't born stupid - I had to study.

  • I have 12 comboboxes to load and I don't want that much C# code to do what I believe can be done with less code using SQL. Here is an SQL Update I've been working with. Two problems: 1. No primary key in truncated temptable_A, 2. I still an null in col_2 of temptable_A, but not near as many as before......

    truncate temptable_A

    update temptable_A

    set col_2 = table_B.col_2

    from temptable_A

    Inner Join table_B

    On temptable_A.PriKey

    = table_B.PriKey

    where table_B.col_2 is not null

  • Dude :: 1 select/combo. End of story. It's the third time we say it and the last in my case. Good luck with any other solution you have in mind.

  • Gotta go with Remi on this one, (although I don't use the word "Dude", unless maybe in reference to President Bush ).  If there is no Relationship Key for your #TempTable, you will be chasing your back end around on this one to populate these combo boxes without NULLs. 

    If you really, really want to do a one fell swoop single select, try the other two methods I suggested...  

    I wasn't born stupid - I had to study.

  • How the heck would that help?? you still need to populate each combo by filtering that query somehow, that assuming that the cost of building that massive temp table is 0.

  • You have one call for the dataset versus 12 calls. 

    I wasn't born stupid - I had to study.

  • Still takes 12 queries to populate the dataset (at least one per col) + 1 to call that one, not to mention the inserts in the table...

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply