select t1.*
from MyTable as t1
where
exists (select 1 from MyTable as t2
where (t2.ID <> t1.ID) and (t2.ColumnA = t1.ColumnA)) and
exists (select 1 from Mytable as t3
where (t2.ColumnB = t1.ColumnA))
I do not understand your question.
The sample data you posted does not have any rows with the values A = 2, B = 3 (or A = 3, B = 2). And the line A = 1, B = 5 has no connection with the previous line.
DECLARE @VAL INT
SET @VAL = 3
;WITH CTE AS(
SELECT A, B FROM YOURABLENAME WHERE A = @VAL
UNION ALL
SELECT T.A, T.B FROM YOURABLENAME T INNER JOIN CTE C ON T.A = C.B
)
SELECT * FROM CTE
mnmhemaj
Member
197 Points
165 Posts
sql query
Apr 25, 2012 02:42 PM|LINK
I need one sql query for the following one.
A table has columnA, ColumnB , etc...
First I check if a value is present in the column A, then I return the row.
Then I take the ColumnB value from the previous result and check if it is also present in A. If so, I return the row.
adamturner34
Contributor
3890 Points
971 Posts
Re: sql query
Apr 25, 2012 02:51 PM|LINK
Select * from myTable where columnA is not null and ColumnB is not null
swapna.anu
Contributor
2658 Points
745 Posts
Re: sql query
Apr 25, 2012 03:01 PM|LINK
Hi
As per my understanding if a table is like below
ColA ColB
------------------
1 2
2 3
3 4
4 5
6
Then the result Should be like below
ColA ColB
------------------
2 3
3 4
4 5
If this is what is expected then the query should be lik
Hope this helps.
Thanks.
ramiramilu
All-Star
95413 Points
14106 Posts
Re: sql query
Apr 25, 2012 03:04 PM|LINK
SELECT * INTO #TempTable FROM Table1 where ColumnA = 'Value'
SELECT * from Table1 WHERE ColumnB IN (Select ColumnA from #TempTable)
Thanks,
JumpStart
silvioyf
Participant
1754 Points
340 Posts
Re: sql query
Apr 25, 2012 03:17 PM|LINK
Try something like that:
select t1.* from MyTable as t1 where exists (select 1 from MyTable as t2 where (t2.ID <> t1.ID) and (t2.ColumnA = t1.ColumnA)) and exists (select 1 from Mytable as t3 where (t2.ColumnB = t1.ColumnA))Hope this helps.
mnmhemaj
Member
197 Points
165 Posts
Re: sql query
Apr 25, 2012 04:22 PM|LINK
Hi
The table looks like:
When I check for the value 3 in ColumnA, the result must be as follows:
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: sql query
Apr 25, 2012 04:29 PM|LINK
You also said, "Then I take the ColumnB value from the previous result and check if it is also present in A. If so, I return the row."
Now, the third row of your desired output is the first row of tables A and B. That means there is no previous row. Something doesn't compute.
mnmhemaj
Member
197 Points
165 Posts
Re: sql query
Apr 25, 2012 04:42 PM|LINK
A and B are column names, not table names.
silvioyf
Participant
1754 Points
340 Posts
Re: sql query
Apr 25, 2012 05:23 PM|LINK
I do not understand your question.
The sample data you posted does not have any rows with the values A = 2, B = 3 (or A = 3, B = 2). And the line A = 1, B = 5 has no connection with the previous line.
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: sql query
Apr 25, 2012 05:55 PM|LINK
Recursive CTE would do the trick for you
Sandeep Mittal | My Blog - IT Developer Zone