sqlite add tag
I have two tables: `Objects` and `Properties`. Each object has some number of properties. This data is stored in a separate table `ObjectProperty`:


I want to collect full information about objects (`id`, `name`, and all its properties as well) by a given `value` of a property.



I was able to write a query that returns data but I'm puzzled with applying a filter separately to each "group" of rows that describes one object.

For example, for `Property1` expected output will be:

Id	Name	pId	Value
1	Object1	1	Property1
1	Object1	2	Property2
1	Object1	3	Property3
2	Object2	1	Property1
2	Object2	4	Property4
2	Object2	5	Property5
4	Object4	1	Property1
4	Object4	4	Property4
4	Object4	5	Property5

and for `Property6`

Id	Name	pId	Value
3	Object3	2	Property2
3	Object3	6	Property6

Top Answer
Jack Douglas
This is a perfect example of where "Window Functions" are useful.

@@@ question 1141

You can "partition" the source rows by `Id` and use the window function variant of the `sum` function to test whether 'Property1' is present in any rows of the partition:


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.