Posted by kishjeff on 12/04/07 15:16

On Dec 4, 8:56 am, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
> On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish <kishjj...@charter.net>
> wrote:
>
> It creates a single column with concatenated data. You could use any
> concatenator (if that's a word) e.g. verticalbar or tilde that is not
> used in the actual data.
>
> -Tom.
>
>
>
> >On Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy
> ><monojita...@gmail.com> wrote:
>
> >>On Dec 4, 9:15 am, Jeff Kish <kishjj...@charter.net> wrote:
> >>> Can anyone tell me how to do this in sql server?
> >>> I am currently doing this query in oracle:
>
> >>> select table1.col1,table1.col2,table2.col3,table4.col4
> >>> where table1.col1 = table2.col3 and
> >>> table2.col3 = table4.col5 and
> >>> (table1.col1,table1.col2) not in
> >>> select table2.col4,table2.col5 from table2
>
> >>> it is the where two column values from any row are not found in any
> >>> row in table2 part that I can't figure out.
>
> >>> thanks
> >>> Jeff
>
> >>It can be done slightly different way,
>
> >>select table1.col1,table1.col2,table2.col3,table4.col4
> >> where table1.col1 = table2.col3 and
> >> table2.col3 = table4.col5 and
> >> cast(table1 as varchar(20)) +'-'+cast(col1 as varchar(20)) not in(
> >> select cast(table2.col4 as varchar(20)) + '-' + cast(table2.col5 as
> >>varchar(20)) from table2)
>
> >>Regards
> >> Monojit
> >thanks. I take it this '-' stops the data from accidentally
> >matching?
> >Jeff- Hide quoted text -
>
> - Show quoted text -

I guess that is the 'key', i.e. the character has to be absolutely not
in the data or it has the potential (small but real) to fail, right?

thanks
Jeff

[Back to original message]