Author |
Topic |
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2013-04-22 : 05:14:46
|
Hi,How can I place the like inside this sql:where ltrim(rtrim(tbl2.Name)) NOT IN (select ltrim(rtrim(Name)) from tblMain group by Name)to become:where ltrim(rtrim(tbl2.Name)) NOT LIKE IN (select ltrim(rtrim(Name)) from tblMain group by Name)?Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 05:21:24
|
should convert to a join..tbl2JOIN (SELECT ltrim(rtrim(Name)) AS Name from tblMain group by Name) mON ltrim(rtrim(tbl2.Name)) NOT LIKE '%' + m.Name + '%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-22 : 05:34:34
|
That should be...LEFT JOIN (SELECT ltrim(rtrim(Name)) AS Name from tblMain group by Name) mON ltrim(rtrim(tbl2.Name)) LIKE '%' + m.Name + '%'WHERE m.Name IS NULL--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 05:47:12
|
in that case this is betterwhere NOT EXISTS (SELECT 1 FROM tblMain WHERE ltrim(rtrim(Name)) LIKE '%' + ltrim(rtrim(tbl2.Name)) + '%')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2013-04-22 : 06:50:05
|
quote: Originally posted by visakh16 should convert to a join..tbl2JOIN (SELECT ltrim(rtrim(Name)) AS Name from tblMain group by Name) mON ltrim(rtrim(tbl2.Name)) NOT LIKE '%' + m.Name + '%' I get this error now:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 06:57:50
|
can you show your full query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2013-04-22 : 07:04:43
|
quote: Originally posted by visakh16 can you show your full query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
select CustomerName, CustomerShortName, Residency, Residency, Residency, City, 0, 0, isnull(ct.TypeID, 1), 0, 0, 0, 0, 1, 2from tblSSIS as ssis left join CT as ct on ssis.CT = ct.Descriptionwhere ltrim(rtrim(CustomerName)) not like '%' + (select ltrim(rtrim(CustomerName)) from Customers group by CN) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 07:09:45
|
[code]selectCustomerName, CustomerShortName, Residency, Residency, Residency, City, 0, 0, isnull(ct.TypeID, 1), 0, 0, 0, 0, 1, 2fromtblSSISImportCustomers as ssisleft join ClientTypes as ct on ssis.ClientType = ct.Descriptionwhere--ltrim(rtrim(CustomerName)) not in (select ltrim(rtrim(CustomerName)) from Customers group by CustomerName) not exists (select 1 from Customers where ltrim(rtrim(ssis.CustomerName) like '%' + ltrim(rtrim(CustomerName)) +'%')[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2013-04-22 : 07:15:08
|
Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 09:14:21
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|