djbuzz (2/5/2010)

---

Hi

This query does not work in my SQL 2008 Server:

SELECT dbo.ip2Tags.ip, dbo.ip2Tags.MediaId

FROM dbo.ip2Tags LEFT OUTER JOIN

...

Hi Paul

This has turned out to be a real can of worms. I couldn't see anything wrong using the example code and data...

Andeavour (2/4/2010)

---

When I was working at CRS on Dantzic Street they were filming Cracker in the old Maxwell House building before it was turned into The Printworks. Never saw Robbie...

But what if it's correctly "evaluating" only...

Thanks for posting this, Paul, it's made for a very interesting lunchtime.

If you add a third anchor row, like this...

with T(i, rn, lvl)...

The lvl value should be 3 where the parentid value is 3. The windowing function worked fine, but the lvl increment in the recursive part of the CTE didn't.

Thanks for this, Mark. Interesting note - the example query actually does run in compatibility 100 mode.

Edit: On second thoughts, that's quite scary, expecially considering the...

Might have a play with this at home tonight, it looks promising. This method works as a single-query solution but it looks horrible.

Cheers

ChrisM

Hi Steve

As promised.

Here's the multistatement code:

-- create table for results

CREATE TABLE #Iteration (PreferenceId INT, BoxId INT, BallId INT)

-- four iterations to solve:

INSERT INTO #Iteration

SELECT PreferenceId, BoxId, BallId

FROM ( -- x

SELECT...

<<limit the recursive selection to rows that aren't already in our selection>>

Hi Steve

I'm not sure a recursive CTE will easily lend itself to this, because what your recursive query 'sees'...

Matt Miller (#4) (2/1/2010)

---

I'm having a bit of a low-brainpower day, but I think you can achieve what you're looking for using ROW_NUMBER(). If you use whatever you were...

Guess the OP didn't like...

Take it out of the WHERE clause. You must have had it in...

klineandking (1/27/2010)

---

i dont know how alll i want is the result set to grouped on the names,i know its difficult i cannot come up with the solution myself thats why...

Jpotucek (1/27/2010)

---

SELECT a.Contact_info

,b.MEMBER_NUMBER

,c.CLIENT_NUMBER

,a.CONTACT_TYPE_ID

,a.history_YN

FROM FRATERNAL.CONTACTS a

inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID

inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID

inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID

WHERE a.CONTACT_TYPE_ID in ('Email1')

AND...