SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can we do Looping without Using Stored Procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TomHand
Starting Member

Australia
8 Posts

Posted - 04/16/2013 :  10:52:01  Show Profile  Reply with Quote
ok, Her is my problem. I got a table that has 3 columns: C1, C2, C3

C1 - C2 - C3
M - is - M1
N2 - is - N3
M2 - is - M3
M3 - is - M4
N1 - is - N2
M1 - is - M2
N - is - N1

So when user search for "M1", the system will show:
M1
M2
M3
M4

when user search for "M3", the system will show:
M3
M4

when user search for "N", the system will show:
N
N1
N2
N3

Clearly, this kind of query requires some sort of looping, ie searching for any word after "is", then continuously searching for word after that word.

I wanna use normal "Select SQL" in MySQL rather than using Transact SQL to do this task. But I have no clue how to do.

Do we have to use "WITH" keyword?

How to do the above query without using Stored Procedure?

Edited by - TomHand on 04/16/2013 10:55:30

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/16/2013 :  10:57:17  Show Profile  Reply with Quote
dunno how to do it in MySQL

But in MSSQL easiest way to do this is to use common table expression (ie using WITH keyword).

Also this is a MS SQL forum so there are not enough experts on MySQL. so you may be better off trying this in some MySQL forums.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 04/16/2013 :  10:59:03  Show Profile  Reply with Quote
quote:
Originally posted by TomHand

ok, Her is my problem. I got a table that has 3 columns: C1, C2, C3

C1 - C2 - C3
M - is - M1
N2 - is - N3
M2 - is - M3
M3 - is - M4
N1 - is - N2
M1 - is - M2
N - is - N1

So when user search for "M1", the system will show:
M1
M2
M3
M4

when user search for "M3", the system will show:
M3
M4

when user search for "N", the system will show:
N
N1
N2
N3

Clearly, this kind of query requires some sort of looping, ie searching for any word after "is", then continuously searching for word after that word.

I wanna use normal "Select SQL" in MySQL rather than using Transact SQL to do this task. But I have no clue how to do.

Do we have to use "WITH" keyword?

How to do the above query without using Stored Procedure?

Are you trying to write the query in MySQL or T-SQL? If it is MySQL, I don't know who you would do it. But in Transact-SQL, you can use the WITH keyword - i.e., a recursive CTE, like shown below:
declare @searchString varchar(32) = 'M';

;with cte(results) as
(
	select C3 from Tbl where C1 = @searchString
	union all
	select t.C3 from Tbl t inner join cte c on c.C3 = t.C1 
) select * from cte;
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/16/2013 :  11:05:11  Show Profile  Reply with Quote
Not MySQL but in MSSQL, May be you don't need looping

Declare @tab table (col1 varchar(2),col2 varchar(2),Col3 Varchar(2))
Insert into @tab values ('M','is','M1'),('N2','is','N3'),('M2','is','M3'),('M3','is','M4'),('N1','is','N2'),('M1','is','M2'),('N','is','N1')
Declare @search varchar(2)
SET @search='M3'
select Col1 from @tab WHERE LEFT(col1,1) = LEFT(@Search,1)AND col1>=@search
Union
select Col3 from @tab WHERE LEFT(col3,1) = LEFT(@Search,1)AND col3>=@search


Cheers
MIK

Edited by - MIK_2008 on 04/16/2013 11:09:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/16/2013 :  11:07:16  Show Profile  Reply with Quote
this is another method without using CTE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

but again not sure if it will work in MySQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TomHand
Starting Member

Australia
8 Posts

Posted - 04/16/2013 :  11:09:34  Show Profile  Reply with Quote
@MIK_2008
M1, M2... is just a word with number that is easy for u to see. In fact, M1, M2, N1.... can be any string.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/16/2013 :  11:18:44  Show Profile  Reply with Quote
@Tom, Agree, based on given data I just proposed a solution and rest is upto you how do you implement it actually in your real life scenario. :)

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.77 seconds. Powered By: Snitz Forums 2000