sql query

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.

Re: sql query

Select * from myTable where columnA is not null and ColumnB is not null

Re: sql query

As per my understanding if a table is like below

ColA ColB

------------------

1 2

2 3

3 4

4 5

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

select * from table where colB in (select colA from table where colA is not null)

Hope this helps.

Thanks.

Re: sql query

SELECT * INTO #TempTable FROM Table1 where ColumnA = 'Value'

SELECT * from Table1 WHERE ColumnB IN (Select ColumnA from #TempTable)

Thanks,

Re: sql query

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.

Re: sql query

The table looks like:

A	B
1	2
1	5
3	4
4	1
5	2

When I check for the value 3 in ColumnA, the result must be as follows:

A	B
3	4
4	1
1	2
2	3
3	4
1	5
5	2

Re: sql query

mnmhemaj

Hi

The table looks like:

A B

1 2

1 5

3 4

4 1

5 2

When I check for the value 3 in ColumnA, the result must be as follows:

A B

3 4

4 1

1 2

2 3

3 4

1 5

5 2

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.

Re: sql query

A and B are column names, not table names.

Re: sql query

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.

Re: sql query

Recursive CTE would do the trick for you

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

Re: sql query

when I run this query, I get an error near 'WITH'.

I tried also removing the ;, but still the same error.

Re: sql query

What version of sql server you are using? Note, it would work on Sql server 2005 onwards.

Re: sql query

2008

Re: sql query

I have found out how to write the query.

select a.t_mitm mitm, a.t_sitm sitm, a.t_cpha cpha from ttibom010 a where (a.t_mitm in (select t_sitm from ttibom010 where substring (a.t_mitm , 10, 10 ) = '2046000021' ) or substring (a.t_mitm , 10, 10 ) = '2046000021'

But I get a different problem.

this query runs in 30 seconds in SQL Management Studio. But it takes more than 5 min from the ASP.NET page.

This query is running on more than 500000 records. I cannot change the index of the table as this is a baan table. How can I minimize the running time from the ASP.NET application?

Re: sql query

mnmhemaj

I have found out how to write the query.

select a.t_mitm mitm, a.t_sitm sitm, a.t_cpha cpha from ttibom010 a where (a.t_mitm in (select t_sitm from ttibom010 where substring (a.t_mitm , 10, 10 ) = '2046000021' ) or substring (a.t_mitm , 10, 10 ) = '2046000021'

But I get a different problem.

this query runs in 30 seconds in SQL Management Studio. But it takes more than 5 min from the ASP.NET page.

This query is running on more than 500000 records. I cannot change the index of the table as this is a baan table. How can I minimize the running time from the ASP.NET application?

It's hard to read, but this:

where (a.t_mitm in (select t_sitm from ttibom010 where substring (a.t_mitm , 10, 10 ) = '2046000021' )

or substring (a.t_mitm , 10, 10 ) = '2046000021'

looks redundant.

Re: sql query

It isn't redundant; the difference is t_mitm vs. t_sitm.

Put your query in a stored procedure, call the stored procedure from asp.net.

Follow the tips in this article to avoid parameter sniffing:

http://www.sommarskog.se/query-plan-mysteries.html

Re: sql query

I solved it on my own.

Re: sql query

mnmhemaj

I solved it on my own.

Hi,

Would you please share your query? It will help others when they meet similar problems. Thanks a lot.

Thanks.

Re: sql query

First I had this query as as an ASP.NET SQL Datasource Select Command. So the page took more than 15 min to update a gridview.

Then I just ran the query and filled a temporary table with the output records and then I wrote a Select Command from this table to the SQL Datasource .

Now my asp.net page runs in just ½sec. I have not done anything to optimize the running time of ASP.NET page.