• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

sql query

hey guys i have a table and doing a search from the menu table

here the coloums

id                 parent_ id                  title                 url
1                        0                           Products          
2                        1                           Shoes
3                        2                           Size 5              ~/#


now i have my curent sql

select parent_id from menu where (UPPER(title)) like UPPER('%" + name + "%') and url is not null

Open in new window



but if i enter shoes they no results found

please help?

i want to keep this statement

url is not null

couse i only want to display products
0
JCWEBHOST
Asked:
JCWEBHOST
  • 10
  • 4
  • 3
  • +2
1 Solution
 
YZlatCommented:
because url is null for shoes
0
 
YZlatCommented:
try

select parent_id from menu where (UPPER(title)) like UPPER('%" + name + "%') 

Open in new window

0
 
JCWEBHOSTAuthor Commented:
i aslo want to keep this

and url is not null
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
YZlatCommented:
it is in the data you displayed above
0
 
Habib PourfardCommented:
Try the following code:
DECLARE @name VARCHAR(127)
SET @name = 'shoes'

SELECT  parent_id
FROM    menu
WHERE   ( UPPER(title) ) LIKE UPPER('%' + @name + '%')
        AND url IS NOT NULL

Open in new window

0
 
YZlatCommented:
since I have no way of seeing your actual data, variable name might have a trailing space. You could try trimming the variable of a space
0
 
YZlatCommented:
try

select parent_id from menu where (UPPER(title)) like UPPER('%" + LTRIM(RTRIM(name)) + "%') and url is not null

Open in new window

0
 
JCWEBHOSTAuthor Commented:
no results found
0
 
YZlatCommented:
or

select parent_id from menu where (UPPER(title)) like LTRIM(RTRIM(UPPER('%" + name + "%'))) and url is not null

Open in new window

0
 
YZlatCommented:
where are you passing the variable name from? C# code? If so, you could use the built-in Trim function
0
 
YZlatCommented:
just out of curiosity, what gets returned if you remove "and url is not null" part like i originally suggested. Can you try it and tell me what happened?
0
 
Habib PourfardCommented:
my query worked with the data you have provided. are you sure you didn't miss anything and the url is not null?
0
 
JCWEBHOSTAuthor Commented:
i need the url not to be null it is hyper link
0
 
YZlatCommented:
pourfard, it worked for me too becuase in data posted url is null. But jcwebhost says in reality it is not null
0
 
Habib PourfardCommented:
Does the following query return any result?
DECLARE @name VARCHAR(127)
SET @name = 'shoes'

SELECT  parent_id
FROM    menu
WHERE   ( UPPER(title) ) LIKE UPPER('%' + @name + '%')

Open in new window

0
 
YZlatCommented:
jcwebhost, I suggested you just try it and tell me the results so it would be easier to diagnoze the issue.

You do not give enough info for us to be able to figure out the problem and the data you have posted is clearly invalid, otherwise your quesry would have worked
0
 
nishant joshiTechnology Development ConsultantCommented:
select parent_id from menu where (UPPER(title)) like ('%'+UPPER(name)+'%') and url is not null

Open in new window


please try this..
0
 
ralmadaCommented:
To start with, you're using the wildcard incorrectly in your where there:

...UPPER(title)) like UPPER('%" + name + "%') and url is not null ...

should be

...UPPER(title) like '%' + UPPER(name) + '%' and url is not null


Secondly, it seems to me you have a hierarchy there, so I would suggest you try with a recursive query to get all revelant records. So for example in the below if you search for shoes you want record 3 and 4, right?


id                 parent_ id                  title                 url
1                        0                           Products          
2                        1                           Shoes
3                        2                           Size 5              ~/#
4                        2                           Size 6              ~/#

Open in new window


If this is correct, can you please clarify if name is a variable or another column in your table there? Assuming it's a variable. You can give this a try


declare @name varchar(255)
set @name = 'Shoes'

;with cte as (
	select *, 0 as lev from menu
	where upper(title) like '%' + upper(@name) + '%' and url is null
	union all
	select a.*, b.lev +1 from menu a
	inner join cte b on b.id = a.parent_id
)
select id, parent_id, title from cte

Open in new window

0
 
JCWEBHOSTAuthor Commented:
thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 10
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now