SQL Search Query with multiple columns needs to edited

Question

I have a search query including one table and two columns.

Select * 
from Gecoserv _a 
where _a.typeOfEcosystem IN (:typeNameList) 
    AND _a.service IN (:serviceNameList)

It works but it's not giving the result I wanted

Let's say:

typeNameList = { Freshwater, Saltwater, Dunes }
serviceNameList = {Habitat, Food, Recreation }

I want the grid to show only the single combinations (Freshwater-Habitat, Saltwater-Food and Dunes-Recreation etc.) but it shows also other combinations (ex: Freshwater-Recreation).

In other words : The query results show

[0,0],[0,1],[0,2],
[1,0],[1,1],[1,2],
[2,0],[2,1],[2,2].

But I want to see ONLY : [0,0],[1,1],[2,2].

I know that I have to add something to the end of query. I tried many things but couldn't figure out a perfect solution.

Is there anybody who can help me with that?

Regards,

Melih

Answer 1

I think this can't be done, when this is the only information you've got. The relation between the values of the parameters, is their position in their list. There is no option to relate and/or preserve these positions in SQL.

What influence do you have on creating the sql-statement?

If you can iterate over the lists in code and dynamically create the sql, you could come to a solution like this:

SELECT * FROM Gecoserv 
  WHERE typeOfEcosystem = 'Freshwater' AND service = 'Habitat'
UNION
SELECT * FROM Gecoserv 
  WHERE typeOfEcosystem = 'Freshwater' AND service = 'Food'
UNION
...

For an example of how to parameterize this, have a look at this answer.

An alternative: splitting strings

Your main problem is to preserve the "rank"/"position" of the parameters in their lists. This can be done, by replacing your lists by strings and break them up.

I used this example of a splitting a string in sql.

The resulting query (made with this Fiddle) then looks like:

DECLARE 
  @ecoSystems varchar(100) = 'Freshwater,Saltwater,Dunes',
  @services varchar(100) = 'Habitat,Food,Recreation',
  @separator char(1) = ','

SELECT
  [g].[id],
  [g].[typeOfEcoSystem],
  [g].[service]
FROM [dbo].[Split](@separator, @ecoSystems) [e]
INNER JOIN [dbo].[Split](@separator, @services) [s] 
  ON [e].[position] = [s].[position]
INNER JOIN [Gecoserv] [g] 
  ON [e].[part] = [g].[typeOfEcoSystem]
    AND [s].[part] = [g].[service]
ORDER BY [id] ASC

Will this work for your scenario? (Answer: yes, almost...)

And finally without a function

DECLARE 
  @ecoSystems varchar(100) = 'Freshwater,Saltwater,Dunes',
  @services varchar(100) = 'Habitat,Food,Recreation',
  @separator char(1) = ',';

WITH [EcoSystemsAndServices]([posE], [startE], [endE], [posS], [startS], [endS])
AS 
(
  SELECT 
    1, 
    1, 
    CHARINDEX(@separator, @ecoSystems),
    1, 
    1, 
    CHARINDEX(@separator, @services)
  UNION ALL
  SELECT 
    [posE] + 1, 
    [endE] + 1, 
    CHARINDEX(@separator, @ecoSystems, [endE] + 1),
    [posS] + 1, 
    [endS] + 1, 
    CHARINDEX(@separator, @services, [endS] + 1)
  FROM [EcoSystemsAndServices]
  WHERE [endE] > 0
)


SELECT 
  [g].[id],
  [g].[typeOfEcoSystem],
  [g].[service]
FROM [Gecoserv] [g]
INNER JOIN [EcoSystemsAndServices] [ess] 
  ON [g].[typeOfEcoSystem] = SUBSTRING(@ecoSystems, 
                                        [startE], 
                                        CASE WHEN [endE] > 0 THEN [endE] - [startE] ELSE 100 END)
    AND [g].[service] = SUBSTRING(@services, 
                                  [startS], 
                                  CASE WHEN [endS] > 0 THEN [endS] - [startS] ELSE 100 END)
ORDER BY [id] ASC

Answer 2

Without any backing schema or even a database manufacturer, my response will have to make some assumptions about your setup.

Theory

This sounds an awful lot like a Cartesian product. The crux of your problem is that your IN clauses are both independent of one another. Thus, a single record can satisfy both conditions but not be what you want to see in the result set. As an example say we have a record as follows:

id   name          typeOfEcosystem  service
--   ------------  ---------------  ----------
 1   myEcoService  Freshwater       Recreation

Your query, as it is written now, will first take the typeOfEcosystem (Freshwater) and check if it is in your list, which it is. Result: TRUE

Then, the query will take the service (Recreation) and check if it is in the second list. Again, the value is found. Result: TRUE

All conditions evaluate to true, so the row is included in the final result set.

Solution

It sounds to me like you want a multiple column IN clause. Note that this is distinct from your attempt, as the final solution should only involve a single IN keyword. Now, the exact form of the query will depend a lot on your database and what it provides. A solution that should work on any SQL-compliant database is to use string manipulation. A rough example is as follows:

Select * 
  from Gecoserv _a 
 where _a.typeOfEcosystem || '-' || _a.service IN ('Freshwater-Habitat', 'Saltwater-Food', 'Dunes-Recreation')

In this example, '||' is assumed to be concatenation. This is very portable, but it is SLOW. The problem can be tackled much more elegantly, but the exact solution will depend a lot more on your specific database and requirements. For example, a multiple column in clause in Oracle might be as simple as:

Select * 
  from Gecoserv _a 
 where (_a.typeOfEcosystem, _a.service) IN (('Freshwater', 'Habitat'), ('Saltwater', 'Food'), ('Dunes', 'Recreation'));

You may have to massage any of these solutions to get them to play well with your DBMS. Further techniques (and just plain old god reading) can be found at:

1. SQL multiple columns in IN clause

2. Select records where a combinations of columns are repeated using SQLite