Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TomHand
Starting Member
8 Posts |
 Posted - 2013-04-16 : 10:52:01
|
| ok, Her is my problem. I got a table that has 3 columns: C1, C2, C3C1 - C2 - C3M - is - M1 N2 - is - N3 M2 - is - M3 M3 - is - M4 N1 - is - N2 M1 - is - M2 N - is - N1So when user search for "M1", the system will show: M1 M2 M3 M4when user search for "M3", the system will show: M3 M4when user search for "N", the system will show: N N1 N2 N3Clearly, 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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-16 : 10:57:17
|
| dunno how to do it in MySQLBut 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-16 : 10:59:03
|
quote:
Originally posted by TomHand
ok, Her is my problem. I got a table that has 3 columns: C1, C2, C3C1 - C2 - C3M - is - M1 N2 - is - N3 M2 - is - M3 M3 - is - M4 N1 - is - N2 M1 - is - M2 N - is - N1So when user search for "M1", the system will show: M1 M2 M3 M4when user search for "M3", the system will show: M3 M4when user search for "N", the system will show: N N1 N2 N3Clearly, 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; |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-16 : 11:05:11
|
| Not MySQL but in MSSQL, May be you don't need loopingDeclare @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 CheersMIK |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
TomHand
Starting Member
8 Posts |
Posted - 2013-04-16 : 11:09:34
|
| @MIK_2008M1, M2... is just a word with number that is easy for u to see. In fact, M1, M2, N1.... can be any string. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-16 : 11:18:44
|
| @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. :)CheersMIK |
|
|
|
|
|
|
|
|
Can we do Looping without Using Stored Procedure?