Proc Help plz 1

[litton1]

I would like to run three queries in a procedure and return the results as one dataset. The reason I would like to run three is, this is a search procedure for searching a parts list so if the user enters Part1. I would then like to search for exact matches.
Then search for Like 'Part1%' and then Like '%Part1%'

select * from parts where name = pSearchName;
select * from parts where name LIKE 'pSearchName%'
select * from parts where name LIKE '%pSearchName%'

. I would then have three results where exact matches are in one list and another list that begin with Part1 and then anything with Part1 in the third list.

Q 1 Am I going about this the correct way (using three selects)?
Q 2. If this is the correct way, how do I combine the results to become one set of data?

Thanks T


Age is a consequence of experience

 

[r937]

do it with one query:

SELECT please
     , select
     , only
     , those
     , columns
     , you
     , need
     , CASE WHEN name = pSearchName
            THEN 5
            WHEN name LIKE 'pSearchName%'
            THEN 3
            ELSE 1 END AS relevance
  FROM parts 
 WHERE name LIKE '%pSearchName%'
ORDER
    BY relevance DESC

r937.com | rudy.ca

 

[litton1]

Thanks for the quick response and I am sure an excellent solution. I will put some test data in the table and have a bash at implementing the proc and let you know how it goes. thanks again T

Age is a consequence of experience

 

[litton1]

had a go with the sql and had a few problems.
This is the procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS stock.PartsSearch $$
CREATE PROCEDURE stock.PartsSearch( pSearchString varchar (50))
BEGIN

  SELECT p.ItemId,
         i.SalePrice,
         i.Name,
      CASE WHEN name = pSearchString
            THEN 5
            WHEN name LIKE 'pSearchString%'
            THEN 3
            ELSE 1 END AS relevance
  FROM stock.Product p, stock.item i
 WHERE p.ItemId = i.ItemId AND i.name LIKE '%pSearchString%'
ORDER
    BY relevance DESC;

END $$
DELIMITER ;

Problem 1
when i use

call stock.partsSearch('Part 1')

I see no results?

if i put the case statement into a new result tab (MYSQL Query browser)

and i use

 SELECT p.ItemId,
         i.SalePrice,
         i.Name,
      CASE WHEN i.Name = 'part 1'
            THEN 5
            WHEN i.Name LIKE 'part 1%'
            THEN 3
            ELSE 1 END AS relevance
  FROM stock.Product p, stock.item i
 WHERE p.ItemId = i.ItemId AND i.name LIKE '%part 1%'
ORDER BY relevance DESC

I get 2 results but expect three.
Results from name field:
Part 1
Part 1 Part2

Result NOT returned from name field:
Part 2 Part1

Any help appreciated



Age is a consequence of experience

 

[r937]

take a close look, that row has Part1 without a space between the Part and the 1





r937.com | rudy.ca

 

[litton1]

Well spotted! I really should visit the opticians. :-(

I guess that leaves the problem of how I am using the searchstring inside the case in the procedure? does it look correct to you, sry but my sql is not good at best!

Thx for the reply


Age is a consequence of experience

 

[litton1]

sorted!

 SELECT p.ItemId,
         i.SalePrice,
         i.Name,
      CASE WHEN i.Name = pSearchString
            THEN 5
            WHEN i.Name LIKE Concat(pSearchString , '%')
            THEN 3
            ELSE 1 END AS relevance

Age is a consequence of experience