Need help with a complex SQL query

Question

I have a stored procedure that uses this select statement:

SELECT dbo.VendorProgram.id, 
       dbo.VendorProgram.CODE, 
       dbo.Programs.ProgramName 
          + '-' + dbo.Divisions.Division 
          + '-' + dbo.Vendors.Source 
          + '-' + dbo.Mediums.Medium 
          + '-' + dbo.VendorProgram.content 
          AS SourceDetail, 
       dbo.Vendors.Source, 
       dbo.Programs.ProgramName, 
       dbo.Divisions.Division, 
       dbo.Mediums.Medium, 
       dbo.VendorProgram.content, 
       dbo.VendorProgram.url, 
       dbo.VendorProgram.cost, 
       dbo.VendorProgram.Notes, 
       dbo.VendorProgram.StartDate, 
       dbo.VendorProgram.EndDate
FROM dbo.Programs 
RIGHT OUTER JOIN dbo.VendorProgram 
                 ON dbo.Programs.id = dbo.VendorProgram.programID 
LEFT OUTER JOIN dbo.Vendors 
                 ON dbo.VendorProgram.vendorID = dbo.Vendors.mappingID 
LEFT OUTER JOIN dbo.Divisions 
                 ON dbo.VendorProgram.divisionID = dbo.Divisions.id 
LEFT OUTER JOIN dbo.Mediums 
                 ON dbo.VendorProgram.mediumID = dbo.Mediums.id

Basically we have a system that put together a code for a vendor. It is made up of 5 IDs pulled from 5 separate tables. Those tables have actual text, for a vendor's name, a type of medium, etc... that match to each ID. So far nothing truly ground breaking in use here I think.

What I need to do is be able to write a query that uses paramaters, in text, to search those 5 separate tables, and find all the "vendor mappings" that could match. I am still new in the SQL world so I am not quite sure what that query would look like.

As an example of how I want to search. I enter into my search form the text "Face" for the vendor field. I would then expect the query runs a select against the vendor table itself to first find all possible vendor IDs with "face" in the name. Then it would need to select all rows from the combined table that have any of those IDs in them.

Hopefully this makes sense, and is possible. As always thanks for any help.

Answer 1

add

WHERE dbo.Vendors.ID in 
 (select ID From dbo.Vendors where dbo.Vendors.Field LIKE '%'+ @QueryText + '%'

Answer 2

SELECT vp.id, vp.CODE,  
    p.ProgramName + '-' + d.Division + '-' + v.Source + '-' + m.Medium + '-' + vp.content AS SourceDetail, 
    v.Source, 
    p.ProgramName, 
    d.Division, 
    m.Medium, 
    vp.content,  
    vp.url, 
    vp.cost,
    vp.Notes, 
    vp.StartDate, 
    vp.EndDate 
FROM  dbo.Vendors v
LEFT OUTER JOIN  dbo.VendorProgram vp vp.vendorID = v.mappingID 
LEFT OUTER JOIN dbo.Programs p  ON p.id = vp.programID 
LEFT OUTER JOIN  dbo.Divisions d ON vp.divisionID = d.id 
LEFT OUTER JOIN  dbo.Mediums m  ON vp.mediumID = m.id 
WHERE v.Name LIKE '%Face%'

Answer 3

If you want only the rows related to your search, switch to inner joins (or include some where not nulls) and in your join to Vendors, include:

AND Vendor.VendorName LIKE @PassedInParam

Where @PassedInParam would be passed into your proc and would be your search string with a '%' on either side, ie '%face%'