Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Forward checking for a column value in another column in MSSQL. How to?

Posted on 2011-10-17
18
Medium Priority
?
292 Views
Last Modified: 2012-05-12
Hi there;

I got a table as:

ID X
1 2
2 4
5 6
3 1
4 5

What I need is checking for ID column's content whether it occurs in forward manner. What I mean is that as for the first row; id is 1 and that value is reoccuring in the 4th row as 3 1. I need to grab that line.

Similary 5 6 -> 4 5

** Please be aware 1 2 -> 2 4 is not counted as 2 4 comes later than 1 2

How can I do this in MSSQL?

Kind regards.

P.S. What I used as a query is as:

SELECT COUNT(*) FROM MyTABLE A WHERE EXISTS (SELECT * FROM MyTABLE B WHERE B.ID <> A.ID AND B.COLX = A.ID);

SELECT A.ID, A.COLX, B.ID, B.COLX FROM MyTable A, MyTable B WHERE A.ID <> B.ID And B.COLX = A.ID;

but this query also gives back the ** case.
0
Comment
Question by:jazzIIIlove
  • 10
  • 5
16 Comments
 
LVL 6

Accepted Solution

by:
effes earned 1500 total points
ID: 36981475
You can use a common table expression to give each row in the table a row number and then run your query against the CTE.
Like this:
WITH MyTableWithRowNumbers (RowNum, ID, COLX)
AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowNum,
		   ID,
		   COLX
	FROM TABLE_A
)

SELECT A.ID, A.COLX, B.ID, B.COLX
FROM MyTableWithRowNumbers A, MyTableWithRowNumbers B
WHERE A.ID <> B.ID AND
      B.COLX = A.ID AND
      B.RowNum > A.RowNum

Open in new window

Note that it checks whether the RowNum of the "B-part" is higher than that of the "A-part".
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36984737
Hi there;

Thanks for the query, it seems it should work but in my C# code, what I get is that:

Unhandled Exception: System.Data.SqlClient.SqlException: Recursive common table expression 'MyTable' does not contain a top-level UNION ALL operator.

Kind regards.
0
 
LVL 6

Assisted Solution

by:effes
effes earned 1500 total points
ID: 36988496
Hi!

I don't really see why that should happen. I just ran the query against my test table again, just to make sure, and it gave me no error (in Management Studio).
To be recursive, it should reference MyTableWithRowNumbers from within the CTE and I don't see that.
Did you modify the query? Maybe changed some names?
Do you get the same error, when you run it from inside the Management Studio?

If that all leads to nothing, please post a bit of your C# code, so I can try to replicate the issue.

Hope that helps.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36988993
I just changed the table name, and columns and have that exception.
0
 
LVL 6

Expert Comment

by:effes
ID: 36989390
Please post the changes you made.
Did you try running the query in SQL Server Management Studio?
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37000925
Hi there;

Here you go:

ok, same error message in SQL Server Management Studio:

The query I run is as follows:

WITH MyTable(RowNum, ID, SubID)
AS
(
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowNum,
               ID,
               SubID
      FROM MyTable
)

SELECT A.ID, A.SubID, B.ID, B.SubID
FROM MyTable A, MyTable B
WHERE A.ID<> B.IDAND
      B.SubID= A.IDAND
      B.RowNum > A.RowNum

Result: Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'MyTable' does not contain a top-level UNION ALL operator.

Please note that, both ID and SubID forms a composite key, as they both have the primary key signs

Regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37001007
oh no, ok my mistake, it should be as
MyTableWithRowNumbers

The query is working but this is not what I want.

Your query gives the result as:

a b c a

but what I want is that:

a b b a

Please note that; in the given scenario:

1 3
3 4
4 1

It should point out the first line 1 3 as it's in the loop 1->3 3->4 4->1 (start with 1 end with 1)

if this is the case, this is not a loop:

1 3
3 4
5 1

1-> 3 3->4 5->1

From 3 4 the next line shouldn't be start 5 1 but 4 1 to form a loop.

and finally for this case;

1 2
2 4
3 6
4 5
5 1

is a loop; 1->2 2->4 4->5 and 5->1 (start with 1 end with 1). but there is a dummy 3->6 which is "not" killing the loop in fact.

I am trying to modify the query but couldn't find out a way.

Regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37001066
Careful about last 2 issue if you add a statement to where close;

A.SubId =B.ID AND

It will show the one another ones but not the ones having a dummy in between.

So, the issue is that there should be a recursion or stg like that within the query, and not an incremental row check.

What do you think?

Kind regards.
0
 
LVL 6

Assisted Solution

by:effes
effes earned 1500 total points
ID: 37002402
OK, now you've got me confused :)

I think I need some examples.

Example 1: The table has (only) the following content
1 3
3 4
4 1
What is the result you expect?

Example 2: My table has (only) the following content
1 3
3 4
5 1
What is the result you expect?

Example 3: My table has (only) the following content
1 2
2 4
3 6
4 5
5 1
What is the result you expect?

Example 4: My table has (only) the following content
1 2
2 4
3 6
4 5
5 1
1 3
3 4
4 1
What is the result you expect?


BTW: While typing the last example a question popped into my mind: How do guarantee that the order of the entries in the table is correct? The problem is, that if you don't specify an ORDER BY in a query the order of the datasets returned is up to SQL Server. So for instance you are not guaranteed, that the datasets returned are in the same order in which they were entered.
So the question is: Is there anything in your table that keep the datasets in the order you expect them to be? Maybe another column? If not, every further attempt at solving your problem is probably useless.
Sorry I didn't see that earlier.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37003930
Hi there;

Example 1: The table has (only) the following content
1 3
3 4
4 1

Result:
1 3 3 4 4 1 ->loop (starts with 1 ends with 1)
3 4 4 1 -> another loop

Example 2:
1 3
3 4
5 1

Result:
1 3 3 4 5 1 ->no loop as there is no transition between 4 and 5.

Example 3:
1 2
2 4
3 6
4 5
5 1

1 2 2 4 4 5 5 1 -> loop
2 4 4 5 5 1 -> another loop
...

Example 4:
1 2
2 4
3 6
4 5
5 1
1 3
3 4
4 1

1 2 2 4 4 5 5 1 loop
2 4 4 5 5 1 loop
...
for the line starting with 1 3 is out of this question as the left column id is incremental. 3 6 is skipped since from the second line, 2 4, the transition should go to the id value to 4.

So, all in all, I believe that within rownum check, we can only find the successive iteration 1 by 1, but in fact, we have to check from the line to the rest of the other, and note that we are going just forward, not backward.

and if it's possible what i need is the first occurence and the root of the loop, e.g. last example
1 2 2 4 4 5 5 1 loop
I need to see 1 2 as an output

If not possible i can see the all loops inside, ithere must be i think a not +1 iteration, but (recursion?) (3 6 is skipped above) but check for the all rows and check is going forward, not backward.

Regards and thanks for your patience (I think tricky this is...)
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37012994
OK, here is the C# version of this question, context is same but C# only.

The thread is longer than our thread and amazingly passionate,but last couple of the comments give an idea to you. Recall that, that question is complete C# whereas ours is complete MSSQL.

Kind regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37025722
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27402066.html

To clarify this question, the last comment is the accepted answer.

That's a C# question whereas in this question, i am trying to achieve with one MSSQL query.

Can you help?

Regards.
0
 
LVL 6

Expert Comment

by:effes
ID: 37027761
Hi jazzIIIlove,

I'm sorry I didn't respond for quite a while, but I just didn't have the time to really look into it.

After reading your other question, I think I understand what you are looking for. Sadly, after pondering over it for a about an hour, I don't see a solution. That doesn't mean that there is none, it's just like my thoughts keep going round in circles and I'm not getting anywhere. (Which is kinda funny, since you're looking for loops :)
I hope I'll find the time to have a fresh go at it in the next days. If anybody else has an idea, please feel free to step in.

Just out of curiosity: Why do it in SQL when you have a working solution in C#?
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37029582
>>I'm sorry I didn't respond for quite a while, but I just didn't have the time to really look into it.
No problem, no worries.

>>(Which is kinda funny, since you're looking for loops :)
:)

>>Just out of curiosity: Why do it in SQL when you have a working solution in C#?
Just to see the extent of a single MSSQL query.

I believe that it cannot be solved by a single SQL, but something recursive or so

Kind regards.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 37066419
Based on original reading of the question, this would seem like the simple solution:
SELECT ID, ColX
FROM MyTable
WHERE NOT EXISTS (
   SELECT 1 
   FROM MyTable lkup
   WHERE lkup.ColX = MyTable.ID
) OR NOT EXISTS (
   SELECT 1 
   FROM MyTable lkup
   WHERE lkup.ID = MyTable.ColX
)
;

Open in new window


What you are describing and indication of recursion is leading to a different topic, so it would be interesting how exactly the output should be. You show, 1 2 2 4 4 5 5 1 << Does this mean you want one long string as long as it is a loop. You can do this with recursive CTE, but may not really be what you need as that is not giving you the gaps...
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 37099240
within the lists/columns:

ID    ColX
1      3
2      3
3      6
4      5
5      2
6      2
7      9
9      11
10      1
11      13
13      14
14      12
15      19
17      18
19      20
20      16
21      22
22      21
23      23
24      23

SELECT ID, ColX
FROM MyTable
WHERE NOT EXISTS (
   SELECT 1
   FROM MyTable lkup
   WHERE lkup.ColX = MyTable.ID
) OR NOT EXISTS (
   SELECT 1
   FROM MyTable lkup
   WHERE lkup.ID = MyTable.ColX
)
;

The output is as follows:

4      5
7      9
10      1
14      12
15      19
17      18
20      16
24      23

which is not the case I am in search for, there must be loop between columns. Refer to the other questions last comments to figure it out better.

Regards.

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question