CODEWITHSUNDEEP
sqlsql-servert-sql
Shahab Haidar
Shahab Haidar

Reputation: 641

How to use filters in a different table in SQL Server

I have a table for Product:

╔═══════════╦═════════════╦══════════════╗
║ ProjectID ║ ProductName ║ Manufacturer ║
╠═══════════╬═════════════╬══════════════╣
║ 1         ║ ABC         ║ A            ║
╠═══════════╬═════════════╬══════════════╣
║ 1         ║ XYZ         ║ B            ║
╠═══════════╬═════════════╬══════════════╣
║ 2         ║ LMN         ║ C            ║
╚═══════════╩═════════════╩══════════════╝

When ever i have to fetch records according to it's manufacturer. i use below query.

select * from ProductMaster
where (Manufacturer like '%A%' or
       Manufacturer like '%B%' or
       Manufacturer like '%C%')
  and project_id=1

But now manufacturer has been increased (a-z). and i want use it dynamically. I mean i don't want to use filters manually. i want to store these filters in other table and i can fetch these records with a procedure. can someone help me in doing it.

i have created a table for Manufacturer list(Filters).

╔═══════════╦═════════╗
║ ProjectID ║ Filters ║
╠═══════════╬═════════╣
║ 1         ║ A       ║
╠═══════════╬═════════╣
║ 1         ║ B       ║
╠═══════════╬═════════╣
║ 2         ║ A       ║
╚═══════════╩═════════╝

Note - Filters are diffrent for diffrent Projects.

Upvotes: 0

Views: 98

Answers (3)

Dwight Reynoldson
Dwight Reynoldson

Reputation: 960

Something like this will get you going.

The declared table accepts a series of proectids and some characters for the filter. The query can then cross applied with this using a charindex to find a match on the relevant projectid with a manufacturer like the filter you are looking for.

declare @filters table

(
    ProjectID int, 
    Filters nvarchar(5)
);

insert @filters (ProjectID, Filters)
(1, 'A'),
(1, 'B'),
(1, 'C');

select p.* from ProductMaster p
cross apply (select f.projectid, filters from @filters f where where p.projectid=f.projectid and charindex(f.filters,p.Manufacturer,1)>0) x

Upvotes: 0

Qirel
Qirel

Reputation: 26450

Join the filter-table in on the manufacturer-column and join it on LIKE '%filter%' using CONCAT().

SELECT pm.* 
FROM ProductMaster AS pm
JOIN Filters AS f
    ON pm.ProjectID = f.ProjectID AND pm.Manufacturer LIKE CONCAT('%', f.Filters, '%')
WHERE pm.project_id=1

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272106

I suppose it is as simple as:

SELECT * 
FROM ProductMaster
WHERE ProjectID = 1
AND EXISTS (
    SELECT 1
    FROM Filters
    WHERE Filters.ProjectID = ProductMaster.ProjectID
    AND ProductMaster.Manufacturer LIKE '%' + Filters.Filter '%'
)

Upvotes: 1

Related Questions