SQL query filter list within a list

Question

Consider a db with 2 tables like below. CompanyId is a foreign key in Department table pointing to Company. Company names are unique but the department names are not.

Table : Department

+-------+-----------+-----------+-------------------+
|   id  |   name    | CompanyId |       phone       |
+-------+-----------+-----------+-------------------+
|   1   |   Sales   |       1   |   214-444-1934    |
|   2   |   R&D     |       1   |   555-111-1834    |
|   3   |   Sales   |       2   |   214-222-1734    |
|   4   |   Finance |       2   |   817-333-1634    |
|   5   |   Sales   |       3   |   214-555-1434    |
+-------+-----------+-----------+-------------------+

Table : Company

+-------+-----------+
|   id  |   name    |  
+-------+-----------+
|   1   |   Best1   |
|   2   |   NewTec  | 
|   3   |   JJA     |
+-------+-----------+

I have a filter like below. when department name is null (empty) it means all the department id for that company should be included in the result but when there is list it should only include the ones which are listed.

    [ {
        companyName: "Best1",
        departmentName: ["Sales", "R&D"]
      },
      {
        companyName: "NewTec",
        departmentName: ["Finance"]
      } ,
      {
        companyName: "JJA",
        departmentName: null
      } 
    }]

Note: The filter is dynamic (a request to an API endpoint) and may include thousands of companies and departments.

I want a sql query to return all department id which fits in the criteria. for this example the result would be "1,2,4,5". (all department ids except the NewTec Sales department's id (3) are returned)

I'm looking for efficient SQL and/or linq query to return the result. I can loop through companies and filter out departments for each individual one but it means that for each company there would be one trip to database using an ORM. Is there any better way to handle this case?

You want to deal with a variable number of conditions. There are two ways to solve this: (1) Build the query string dynamically from your criteria. (2) Put the criteria in a separate table and query against that table. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 13, 2019 at 5:37

Tim Biegeleisen · Accepted Answer · 2019-09-13 05:14:02Z

1

Here is the SQL query you want:

SELECT
    d.id
FROM Department d
INNER JOIN Company c
    ON d.CompanyId = c.id
WHERE
    (c.name = 'Best1' AND d.name IN ('Sales', 'R&D')) OR
    (c.name = 'NewTec' AND d.name = 'Finance') OR
    c.name = 'JJA';

Demo

answered Sep 13, 2019 at 5:14

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Fred Jand Over a year ago

That request is just a sample, there could be any combination and hundreds of them, it cannot be hard-coded.

Tim Biegeleisen Over a year ago

@Fred Do you think there is a reason why your question and my answer got downvoted?

Thorsten Kettner Over a year ago

@Fred Jand: As there can be one or two or three or thousand conditions, the query will be different each time. Tim is showing the query for the conditions mentioned. Thus you see how to build the query. After all, a query is just a string sent to the DBMS, so build that string in your programming language in order to end up with something like Tim is showing.

Fred Jand Over a year ago

@Tim Biegeleisen, I don't know, I was trying to solve this problem past few hours. I simplified (and altered) data but it is real problem that I could not find a good solution for it.

Tim Biegeleisen Over a year ago

@FredJand If you are asking is how to create a flexible single prepared statement which can cover many different parameter combinations, then there is a way to do that, but your question did not make this clear.

|

Thorsten Kettner · Accepted Answer · 2019-09-13 06:18:35Z

You want to deal with a variable number of conditions. There are mainly two ways to solve this:

Build the query string dynamically from your criteria.
Put the criteria in a separate table and query against that table.

With a filter table as such:

COMPANY_NAME | DEPARTMENT_NAME
-------------+----------------
Best1        | Sales
Best1        | R&D
NewTec       | Finance
JJA          | (null)

The unvarying (!) query would be:

SELECT *
FROM Department d
INNER JOIN Company c ON d.CompanyId = c.id
WHERE EXISTS
(
  SELECT *
  FROM Filter f
  WHERE f.company_name = c.name
  AND (f.department_name = d.name OR f.department_name IS NULL)
);

Here is a demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=49ff15426776536acc6f5bd7f88aaf8f (I've hijacked Tim's demo for this :-)

And here is an idea how to combine the two approaches mentioned: Make the filter table a temporary view, i.e. put it in a WITH clause. But in order to keep the query unvarying, you fill that WITH clause from a stored procedure which does the dynamic part. The stored procedure would read an XML containing the criteria and select rows from it. Thus you may be able to have your ORM call this procedure to get the results you are after.

Here is a thread explaining how to build a query in a stored procedure taking input from an XML: Creating a query from XML input parameter in SQL Server stored procedure and verifying output

The query would then look somethink like:

WITH Filter AS (SELECT company_name, department_name FROM dbm.GetMyFilterDataFromXml(@Xml))
SELECT *
FROM Department d
INNER JOIN Company c ON d.CompanyId = c.id
WHERE EXISTS
(
  SELECT *
  FROM Filter f
  WHERE f.company_name = c.name
  AND (f.department_name = d.name OR f.department_name IS NULL)
);

Collectives™ on Stack Overflow

SQL query filter list within a list

2 Answers 2

Demo

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related