Frank, that is a nice solution.  I typically use "IN" instead of "EXISTS" - which I am trying to get away from.  Anyway, your "EXISTS" statement is confusing me so...

JN, are you getting the "Invalid Column Name" PropTID error on the "IN" clause or the "EXISTS"?

For the "IN" clause, don't you still need to join your "B" table since...

Because of the OR joins the GROUP BY was elliminating duplicates.  I do like your solution without the function better though...

Sorry - that join needed work.  Change your select to this:

SELECT f.Fundname,

dbo.fnGetAccountNumber(Acct1_ID) as Acct1,

dbo.fnGetAccountNumber(Acct2_ID) as Acct2,

dbo.fnGetAccountNumber(Acct3_ID) as Acct3

FROM tempfunds f inner join tempaccount a on f.acct1_id=a.acct_id

OR f.acct2_id=a.acct_id

OR f.acct3_id=a.acct_id

GROUP BY

Fundname,...

Create Function dbo.fnGetAccountNumber(@AccountID int)

Returns BigInt

as

 begin

  declare @AccountNumber Bigint

  set @AccountNumber = (SELECT AcctNumber FROM tempAccount WHERE Acct_id=@AccountID)

  return @AccountNumber

 end

SELECT f.Fundname,

dbo.fnGetAccountNumber(Acct1_ID) as Acct1,

dbo.fnGetAccountNumber(Acct2_ID) as Acct2,

dbo.fnGetAccountNumber(Acct3_ID) as Acct3

FROM funds f

inner join account...

Thanks everyone for the solutions. 

Ryan

Have you tried something like:

Dim oConn As ADODB.Connection

Dim oRS As ADODB.Recordset

Dim strConn As String

DIM strsql As String

Set oConn = New ADODB.Connection

strConn = [ado connection string]

Set strsql = [T-SQL statement]

oConn.Open (strConn)

oConn.Execute...

Run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe

...and enable either NamedPipes, TCPIP, or both.

If you have more than one instance on that machine, make sure you are doing this for the correct instance.

You...

How about this?

select t1.col1 as COL1, t1.col2 as COL2_1, t2.col2 as COL2_2

FROM tbltable t1, tbltable t2

WHERE t1.col1=t2.col1

AND t1.col2<t2.col2

AND t1.col2<>(t2.col2)-2

Ryan

PW, thanks for your help.  Your query selects the same 311 records as my query 1.  I like the way you used exists - I will take a closer look...

Sorry about that.  Yes, many different suites can have the same name in different buildings.  For instance, suite 300 can occur many times (in different buildings)

After some additional testing, it looks like query 1 is correct:

select s.suiteid, s.buildingid, s.SuiteName, b.buildingnumber, g.suitename, g.buildingID

from suites s

inner join buildings b on s.buildingid=b.buildingid

LEFT JOIN RealmStageSuites g on g.buildingid=b.buildingnumber AND...

Are you saying my solution won't work with the values below?

insert sa values(1,100)

insert sa values(1,200)

insert sa values(2,50)

insert sa values(2,100)

insert sa values(3,300)

insert sa values(3,200)

insert sa values(4,300)

insert sa values(4,200)

insert sa values(5,300)

insert sa...

Kishore - I made a newbie mistake.  Most of the execution time is spent keeping track of the counter.  If you have already copied the code, make sure to make...

How about something like this:

___________________________________________________________

declare @@counter integer

declare @@appendsql varchar(1000)

declare @@sql varchar(1000)

set @@counter = 1

set @@sql = 'select '

WHILE @@counter < (select COUNT(distinct ID) from temp)

 BEGIN

  set @@appendsql = 'SUM(CASE ID WHEN...