JCWEBHOST
asked on
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
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
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
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
because url is null for shoes
try
select parent_id from menu where (UPPER(title)) like UPPER('%" + name + "%')
ASKER
i aslo want to keep this
and url is not null
and url is not null
it is in the data you displayed above
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
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
try
select parent_id from menu where (UPPER(title)) like UPPER('%" + LTRIM(RTRIM(name)) + "%') and url is not null
ASKER
no results found
or
select parent_id from menu where (UPPER(title)) like LTRIM(RTRIM(UPPER('%" + name + "%'))) and url is not null
where are you passing the variable name from? C# code? If so, you could use the built-in Trim function
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?
my query worked with the data you have provided. are you sure you didn't miss anything and the url is not null?
ASKER
i need the url not to be null it is hyper link
pourfard, it worked for me too becuase in data posted url is null. But jcwebhost says in reality it is not null
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 + '%')
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
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
select parent_id from menu where (UPPER(title)) like ('%'+UPPER(name)+'%') and url is not null
please try this..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks