Need help with a simple select statement

[thakral]

Hi I have a table with 2 columns s below. What i want to achieve is a lookup like functionality. What i mean is i want to be able to pass '1','7','9' and get back 'Name1','','Name9' Any thoughts on how this can be achieved?

I tried using Select * from table where Col1 in ('1','7','9') but the output i get from that is 'Name1','Name9'.. the gap for 7 is omitted..

Table1
------------
Col1 | Col2
1 | Name1
2 | Name2
3 | Name3
4 | Name4
5 | Name5
8 | Name8
9 | Name9
10 | Name10

thanks
Anant

 

[fredericofonseca]

what are you trying to do exactly?

Just mentioning "lookup" functionality is not clear on the destination of the data, and that is what can make a difference.

Cant think of a way of doing this without a SP and a cursor, and that will slow down things a lot depending on the size of the table and on the number of records returned.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[thakral]

Hi Fredrico

Do you mean this cannot be done using select but can be dont using a stored procedure? is it possible for you to provide an example?

What i am trying to achieve is simply to get the corresponding values from Col1 in Col2 and if it does not exist return a '' instead of skipping the entry which happens when using select IN as mentioned above.

Thanks
anant

 

[fredericofonseca]

you can not return a record that is not in the table.

So a straight sql on that table just to report existing and missing numbers does require a SP with a cursor, where the SP returns a recordset.

Now if you are using a client application (VB/C, other), then that processing should be done on the client side.


Another option that just though of. Has the overhead of the temp table.

Create a temp table with all numbers on it. then join it with your table with the following sql.
Select f2.col1,isnull(f1.col2,'') as col2
from table f1
inner join number_table f2
on f1.Col1 = f2.col1
where f2.Col1 in ('1','7','9')


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

oops.

join should be "right outer join"

Or swap the tables, and to a "left outer join"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bborissov]

You can't do that with "simple" query. What you need is to create a function wich will split your variable into records, join returned table and get the results:

CREATE FUNCTION SplitVar
(
@ListVar varchar(8000),
@SplitChar varchar(1)
)
RETURNS @Test TABLE (Fld1 int)
AS
BEGIN
   DECLARE @i int
   WHILE LEN(@ListVar) > 0
         BEGIN
            SET @i = CHARINDEX(@SplitChar,@ListVar)
            IF @i = 0 AND LEN(@ListVar) > 0
               BEGIN
                  INSERT INTO @Test VALUES (CAST(@ListVar as int))
                  SET @ListVar = ''
               END
            ELSE
               BEGIN
                  INSERT INTO @Test VALUES (CAST(SUBSTRING(@ListVar,1,@i-1) as int))
                  SET @ListVar = SUBSTRING(@ListVar,@i+1,8000)
               END
         END
    RETURN
END


DECLARE @Test Table (Col1 int, Col2 varchar(8000))
INSERT INTO @Test  VALUES(1,'Name1')
INSERT INTO @Test  VALUES(2,'Name2')
INSERT INTO @Test  VALUES(3,'Name3')
INSERT INTO @Test  VALUES(4,'Name4')
INSERT INTO @Test  VALUES(5,'Name5')
INSERT INTO @Test  VALUES(8,'Name8')
INSERT INTO @Test  VALUES(9,'Name9')
INSERT INTO @Test  VALUES(10,'Name10')



SELECT ISNULL(Test.Col1,Tbl1.Fld1) AS Col1,
       ISNULL(Test.Col2,'') AS Col2
       FROM @Test Test
       RIGHT JOIN SplitVar('1,2,7',',') Tbl1 ON Test.Col1 = Tbl1.Fld1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP