asked on

Need a query to coalesce multiple values n levels deep

Given a table that looks like the following:

I need to construct a query that will produce the effective properties for a given object. A matrix to represent this might look like this:

The rules are as follows:
* The inheritance order is known at the time the query is run. It is not guaranteed to be in any sorted order.
* The inheritance list may be anywhere from 2 to n layers deep
* The first non-null property becomes the effective property.
So, for Id 203 the effective properties would be:
Width: 500
Height: 200
Color: Blue
Font: Helvetica

For Id 47 the effective properties would be:
Width: 600
Height: 200
Color: Black
Font: Arial

I need a query that will accept a list of IDs of arbitrary length and coalesce all the property values to achieve a table with the effective properties of that object. Anyone know how I might do this?

I'm not sure if I'm asking this question the right way so please feel free to ask for clarification.

HainKurt

I dont get your logic and your sample result...
are those correct values based on your logic? check again... for example I dont see any arial font anywhere but in your result...

Russ Suter

ASKER

The object with ID 47 has no Font property defined so it inherits the property value of its parent which in the above case is ID 0 (read left to right).

HainKurt

in your first chart I dont get

0 font Helvetica

or

For Id 47 the effective properties would be:
Width: 600
Height: 200
Color: Black
Font: Arial

they are against your logic I guess...
check your inputs, img 1 and 2

Russ Suter

ASKER

In the first chart is the actual table data. It means that ID 0 has a property called Font with the value of "Helvetica"

The logic is consistent if you follow the matrix left to right. Pick the starting ID and read each row left to right starting with that ID. The first non-null value is the effective property for that ID.

HainKurt

4th row in first table/image is not fitting into the logic :)
4th row, 2nd col should be Arial not Helvetica
or I am still having issues understanding this...

Russ Suter

ASKER

DOH! 4th row, 1st table is a typo, it should read "Arial"

HainKurt

here

with t as(
select 0 id, 'Width' PropertyName, '400' value
union all select 0, 'Height', '200'
union all select 0, 'Color', 'black'
union all select 0, 'Font', 'Arial'
union all select 12, 'Width', 'Arial'
union all select 12, 'Color', '500'
union all select 47, 'Width', '600'
union all select 203, 'Font', 'Helvetica'
)
select * from (
select *, row_number() over (partition by PropertyName order by id desc) rn
from t
where id in (0, 203)
)x where rn=1

id	PropertyName	value	rn
0	Color	black	1
203	Font	Helvetica	1
0	Height	200	1
0	Width	400	1

Open in new window

HainKurt

or maybe this

select * from (
select *, row_number() over (partition by PropertyName order by id desc) rn
from t
where id <=203
)x where rn=1

id	PropertyName	value	rn
12	Color	500	1
203	Font	Helvetica	1
0	Height	200	1
47	Width	600	1

Open in new window

I could not get the inheritance logic :)

Russ Suter

ASKER

Those queries are messy and use hard-coded values so I'm afraid I can't use them.

Basically, there's a table elsewhere that maintains an inheritance list. That list can be in any order and of any size. I need to be able to construct an effective property list based on the inheritance list provided. The above example shows an order of 203, 12, 47, 0 but it could just as easily be 0, 12, 47, 203 or 12, 47 or 1, 2, 3, 4, 5, 6, 8, 42.

HainKurt

without inheritance logic, you cannot get what you want... or do you really know what to get without that inheritance table :)

I gave you 2 samples...

one is id descending (203 < 47 < 12 < 0)
the other is id + 0 descending (203 < 0)

use my sample queries and join it with your inheritance table...

Russ Suter

ASKER

Let's assume I have an inheritance table (which I do). The sample queries you provided are too rigid to be able to support using that table as an input. The first example uses a bunch of union statements which is fine for a known, finite set but not in this case. The second example fails to account for the order of inheritance which is not guaranteed to be ascending or descending.

What I need is a query that can handle an arbitrary number of Id values in an arbitrary order.

HainKurt

The first example uses a bunch of union statements which is fine for a known, finite set but not in this case.

that is for test/demo data... in your case you will use just your table and this

select * from (
select *, row_number() over (partition by PropertyName order by id desc) rn
from yourTable t
where id in (0, 203)
)x where rn=1

Open in new window

and second one is similar

select * from (
select *, row_number() over (partition by PropertyName order by id desc) rn
from yourTable t
where id <=203
)x where rn=1

Open in new window

Russ Suter

ASKER

Still not what I'm after. I can write queries like that in my sleep. They still take no account of inheritance order nor can they properly handle a list of size n.

HainKurt

without inheritance logic, I cannot help further...

and I dont get what you mean by

I can write queries like that in my sleep. They still take no account of inheritance order nor can they properly handle a list of size n.

maybe you should explain it better with sample data
or maybe you should re-phrase this question when you are awake :)

Russ Suter

ASKER

Touche!

I'm obviously not explaining this well. Let me take some time to rethink how I could ask this question better.

HainKurt

when you say, you get a list of ids... are those the inheritance logic, or just a list of ids that you want to get properties and values...

and without inheritance logic/data/table, how can you get the correct data?
do you know what the correct data without using the inheritance?
if you dont guess what the result is without that knowledge, it means you cannot write code...

no logic, no code, simple as that

Russ Suter

ASKER

I have the inheritance order figured out already from a separate table which tells me what an item inherits from and in what order. Standby, I think I'm on to something but might need a bit of help getting past the finish line.

Russ Suter

ASKER

OK, I'm onto something. This image is a real example of the data I'm working with. I have a query working very nicely to produce this result.

The specific question now is, how do I get the first row of each Property_Id. The resulting set should look like this:

You'll notice that I've filtered out all but the first occurrence of the value of Property_Id and the resulting set contains exactly one row for each Property_Id value.

As an added bonus, if possible the query needs to be compatible with both Microsoft T-SQL and SqLite syntax.

Russ Suter

ASKER

For additional reference, this is the query I'm using to get the first data result seen above.

DECLARE @Element_Id INT
SET @Element_Id = 2545

SELECT
	[Property_Id],
	[Name] AS [PropertyName],
	[Value] AS [PropertyValue],
	[PropertyBag].[InheritsFrom_Id],
	[PropertyBag].[SortOrder]
FROM
	[dbo].[ElementProperty]
INNER JOIN
	(
	SELECT
		[Element_Id],
		[InheritsFrom_Id],
		[SortOrder]
	FROM
		[dbo].[ElementInheritance]
	UNION
	SELECT
		@Element_Id,
		@Element_Id,
		0
	UNION
	SELECT
		@Element_Id,
		0 - (SELECT [ElementType_Bitmask] FROM [dbo].[Element] WHERE [Id] = @Element_Id),
		2147483647
	FROM
		[dbo].[ElementInheritance]
	) AS [PropertyBag]
	ON [PropertyBag].[InheritsFrom_Id] = [ElementProperty].[Element_Id]
INNER JOIN
	[dbo].[ElementPropertyMaster] ON
	[dbo].[ElementPropertyMaster].[ElementType_Bitmask] & 32 = 32
	AND [dbo].[ElementPropertyMaster].[Id] = [Property_Id]
ORDER BY
	[Property_Id],
	[PropertyBag].[SortOrder]

Open in new window

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

or using cte

with t as (your query here)
select *, row_number() over (partition by PropertyName order by Property_ID) rn
  from t where rn=1
 order by Property_ID

Open in new window

ASKER CERTIFIED SOLUTION

Russ Suter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Russ Suter

ASKER

Thanks for your help. Talking it through was useful.