Getting just one row from a set of many possible rows.

[rhtiwari]

I am doing a join across three tables and the third table has multiple rows for the field on which it is joined. I need just one (any one is fine) of the rows. Is it somehow possible to do it?

Thanks!

 

[DotNetGnat]

multiple rows??

try using DISTINCT key word...

also please show us some sample data...so that someone can suggest you the correct solution...

-DNG

 

[rhtiwari]

The simplified scenario is something like this:
There is a client table with the client details.
Each client is associated with a marketing group
which may have one or more relationship managers.

I needed (previously, the situation is more complicated now)just one (any one) of the relationship managers for the client.

However, the situation now is that I want a single record with the names of all the RMs in a comma separated list.

Is it possible to do it with just SQL?

R

 

[JMCraig]

Hi,

Yes, it's possible to do just with SQL, but if you have a reporting package that allows you suppress duplicated elements and you don't strictly need the names in a comma-delimited list, I'd go with that. (Crystal Reports, for example, makes this easy.)

If you want to do it in SQL, create a temp table (referred to as temp_table below) with the basic client info and an empty varchar column for the RM names (rm_names in the example below), then use a two level loop to walk through each RM for each client and add the name. It'd follow a pattern something like this:

declare @rm_name varchar( 80 ) /* size to suit */
      , @client_key int /* or whatever you need */
 select @client_key = min( client_key )
   from temp_table

 while ( @client_key is not null ) begin
    select @rm_name = min ( rm_name )
      from rm_table
    while ( @rm_name is not null ) begin
      /* if not first name, put in a comma-blank */
      update temp_table
         set rm_names = rm_names + ', '
       where client_key = @client_key
         and datalength( rm_names ) > 0 
      /* add the current name to list */
      update temp_table
         set rm_names = rm_names + @rm_name
       where client_key = @client_key

      /* now get the next name
      select @rm_name = min( rm_name )
        from rm_names
       where client_key = @client_key
         and rm_name > @rm_name
    end /* while got a value in @rm_name

    /* get the next client key */
    select @client_key = min( client_key )
      from temp_table
     where client_key > @client_key
  end /* while got a value in @client_key

Now, at this point, you should be able to do a SELECT * from the temp table and you're set. As you can see, this isn't a relational approach and it's not going to be efficient. But if the comma-separated list is absolutely what you need, it should get you there.

HTH!

John Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com