How to merge lines in an SQL query?

Question

MY table looks like this

id | string | foreign_id
---------------------------
1  | house  | 5
2  | garden | 6
3  | window | 5
...

I have an array of strings and i want to get all foreign ids which match all elements in the array. So i have an array like this [house, window] then i want to get 5. The array of strings can have up to 10 elements.

How does the appropriate sql statement look like?

Answer 1

select string, foreign_id
from table
group by foreign_id

Answer 2

Assuming you want foreign_id that match both "house" and "window", use:

  SELECT t.foreign_id
    FROM YOUR_TABLE t
   WHERE t.string IN ('house', 'window')
GROUP BY t.foreign_id
  HAVING COUNT(DISTINCT t.string) = 2

The HAVING count must equal the number values defined in the IN clause.

The array of strings can have up to 10 elements.

That will require using dynamic SQL. I'd provide an example, but you didn't mention what database you're using...

Answer 3

Generate the query dynamically, adding the noted line once for each array element:

Select distinct foreign_id
 from myTable
where 1=1
  --add the following line once for each array element
and foreign_id in (select foreign_id from myTable where string = 'value')

So given your example, the query would be

Select distinct foreign_id
 from myTable
where 1=1
and foreign_id in (select foreign_id from myTable where string = 'house')
and foreign_id in (select foreign_id from myTable where string = 'window')

This should return what you're looking for.

Any questions?

// the 1=1 is just so that the dynamic part is the same each time.. I would exclude the 1=1 and have the first entry skip the 'and'

Answer 4

Try something like:

select foreign_id 
from your_table
where string in ('house', 'window')
group by foreign_id
having count(distinct string) = 2;

- assuming that whatever generates the query can count the number of distinct strings requested.

(Edited following comments)

Answer 5

@Mark Bannister was close, but his will fail when there are duplicates of string and foreign_id. This works, but it's been years since I did anything useful with SQL so there's probably a better way.

SELECT foreign_id FROM (
   SELECT distinct string, foreign_id FROM your_table
      WHERE string in ('house', 'window')) as T  
GROUP BY T.foreign_id HAVING count(foreign_id) = 2;

Like Mark, I assume that you can dynamically populate the in clause as well as the RHS of the having clause. A stored procedure might help here.