Creating inner join By using Like Operators

Question

I am developing a general store management in java fx using SQL Server DBMS. I have to store the data in the table so that I have to put a text field to search data for that purpose I have to search through SQL in database:

CREATE PROC Spgetproductlistwithsearch @value NVARCHAR(max) 
AS 
  BEGIN 
      SELECT p.product_id, 
             p.NAME, 
             pt.type, 
             p.pur_price, 
             p.sale_price, 
             p.stock, 
             s.NAME, 
             s.contact_no, 
             s.email 
      FROM   ((product AS p 
               INNER JOIN producttype AS pt 
                       ON p.type_id = pt.type_id) 
              INNER JOIN supplier AS s 
                      ON p.supplier_id = s.supplier_id) 
      WHERE  p.NAME LIKE @value 
              OR s.NAME LIKE @value 
              OR pt.type LIKE @value 
              OR p.pur_price LIKE @value 
              OR p.sale_price LIKE @value 
              OR p.stock LIKE @value 
              OR p.product_id LIKE @value 
              OR s.contact_no LIKE @value 
              OR s.email LIKE @value 
  END; 

EXEC Spgetproductlist

but it is not giving me the expected values it is giving me two tables but I need join of all these tables

We have neither your actual result nor your expected result. That makes it rather hard to understand your question. — Gordon Linoff, Dec 10 '18 at 11:36

Zeki Gumus · Answer 1 · 2018-12-10T10:04:12.420

Try This :

You can check following link for more detail : Usinig like operator with stored procedures parameters

CREATE PROC spGetProductListWithSearch 
        @value nvarchar(MAX)
    AS
    BEGIN
        SELECT
            p.Product_id
           ,p.Name
           ,pt.Type
           ,p.Pur_Price
           ,p.Sale_Price
           ,p.Stock
           ,s.Name
           ,s.Contact_No
           ,s.Email
        FROM ((Product AS p
        INNER JOIN ProductType AS pt
            ON p.Type_Id = pt.Type_Id)
        INNER JOIN Supplier AS s
            ON p.Supplier_Id = s.Supplier_Id)
        WHERE p.Name LIKE '%'+@value+'%'
        OR s.Name LIKE '%'+@value+'%'
        OR pt.Type LIKE '%'+@value+'%'
        OR p.Pur_Price LIKE '%'+@value+'%'
        OR p.Sale_Price LIKE '%'+@value+'%'
        OR p.Stock LIKE '%'+@value+'%'
        OR p.Product_id LIKE '%'+@value+'%'
        OR s.Contact_No LIKE '%'+@value+'%'
        OR s.Email LIKE '%'+@value+'%'
    END
    GO
    EXEC spGetProductListWithSearch
        @value = 'test'

Thanks so much. I am really happy to be the part of this expert community. — Software Engineer, Dec 10 '18 at 16:33

Creating inner join By using Like Operators

1 Answers1