Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Posts
    4

    Unanswered: Comparing two row values

    whats the best way to compare two row values and concatenate those two

    For example

    Table1
    -------
    Col1 Col2
    ----------
    A 1
    A 4
    B 2
    C 3
    A 5

    I need a query to return something like
    "where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

    Thanks in advance
    Last edited by raj48000; 01-15-07 at 18:06.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    try using cursor. one single SQL will not do the job.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If I understand what you're asking, there is no need to concatenate, and certainly no need for a cursor.

    What I think you want to do is SELECT rows from a table or view based on the values of four different columns (a, b, c, and d). In your case, you need to see the rows that meet all three criteria: Column A values of 1, 4, or 5 and also have Column B = 2 and Column c = d.

    If I understand your question, then I'd use:
    Code:
    SELECT *
       FROM myTable
       WHERE  (a = 1 OR a = 4 OR a = 5)
          AND b = 2
          AND c = d
    -PatP

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need to explain more. The way I see it you want
    Code:
    select * from Table1
    where (Col1='A' and Col2 in (1,4,5))
       or (Col1='B' and Col2=2)
       or (Col1='C' and Col2=3)

  5. #5
    Join Date
    Apr 2005
    Posts
    4
    Ok let me elaborate

    I have a table with two columns with the values

    Col1 Col2
    ----------
    A 1
    A 4
    B 2
    C 3
    A 5

    The data mentioned above is just the sample, I dunno wats its going to be in

    I am looking for a query and that should form the where clause like
    "where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

    which means it should find the identical ones first and concate with or clause and rest join with the and clause. In this case we have three A's so it should form (A=1 or A=4 or A=5) and then and clause to (b=2) and (c=3)

    Thanks in advance

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think I get what you're after.

    Where do you need to plug in this "where" clause when you're done? Perhaps there's a cleaner way to get the job done.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    this only froms the string that u r looking for using cursor (i can hear some screaming!!). u know better what to do with that string

    Code:
    declare @Str char(1)
    declare @LastStr char(1)
    declare @Num smallint
    declare @Out varchar(200)
    set @Out = '('
    declare Cur1 cursor for select Str,Num from theTable order by str,num
    open Cur1
    fetch next from Cur1 into @Str,@Num
    while @@fetch_status = 0
    begin 
    	if (@LastStr is not null) 
    	begin	
    		if(@LastStr <> @Str)
    			set @Out = @Out + ') and ('
    		else
    			set @Out = @Out + ' or '
    	end
    	set @Out = @Out + @Str + '='+ cast(@Num as varchar)
    	set @LastStr = @Str
    	fetch next from Cur1 into @Str,@Num
    end
    close Cur1
    deallocate Cur1
    set @Out = 'where ' + @Out + ')'
    print @Out

  8. #8
    Join Date
    Apr 2005
    Posts
    4
    thanks upalsen, but I am thinking is there any other way to do this without cursors. Your code definetly works but looking for alternative. Thanks

  9. #9
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    as i told earlier, it is possible without cursor as well. but that is neither going to look smarter not would perform better. performance of course is dependent on many other factors that is not clear to us form the post. if it is a onetime affair u can use cursor without any doubt. however, if u r still interested in cursor-less sql, let me know.

  10. #10
    Join Date
    Apr 2005
    Posts
    4
    thanks upalsen, yes please let me know how can we do this without cursors, I did use the cursors and its working fine, but just want to know how can we do without cursors. I appreciate your help.

    Thanks in advance.

  11. #11
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Code:
    select Col1,Col2 into #TT from theTable order by Col1,Col2
    update #TT set #TT.Col1 = #TT.Col1+'_Max' /*expecting the width sufficient to hold 5 chars, else add col/change width*/ from #TT, (select Col1,max(Col2) as MaxCol2 from #TT group by Col1) as TT2 where #TT.Col1 = TT2.Col1 and #TT.Col2=TT2.MaxCol2
    declare @sql varchar(200)
    set @sql = '('
    select @sql = @sql + left(Col1,1) + '=' + cast(Col2 as varchar) + case when len(Col1)>1 then ') and (' else ' or ' end from #TT
    set @sql = 'where ' + left(@sql,len(@sql)-6)
    print @sql
    drop table #TT
    looks meaningless though...cant find anything better than this.....
    Last edited by upalsen; 01-19-07 at 02:10.

  12. #12
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I like a challenge. I came up with this one...
    Code:
    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
    CREATE TABLE #tmp (Col1 CHAR(1), Col2 CHAR(1))
    INSERT #tmp VALUES ('A','1')
    INSERT #tmp VALUES ('A','4')
    INSERT #tmp VALUES ('B','2')
    INSERT #tmp VALUES ('C','3')
    INSERT #tmp VALUES ('A','5')
    INSERT #tmp VALUES ('C','1')
    
    DECLARE @sql VARCHAR(1000)
    SET @sql = '('
    
    SELECT @sql = @sql + CASE
                            WHEN Col1 IS NULL THEN ''
                            WHEN Col2 IS NULL THEN ') AND ('
                            ELSE Col1 + '=' + Col2 + ' OR '
                         END
    FROM   #tmp
    GROUP BY Col1, Col2
    WITH ROLLUP
    
    SELECT REPLACE(LEFT(@sql, LEN(@sql) - 6), ' OR )', ')')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •