How to select parts that have messages based on feature id and feature value ?
-
I work on sql server 2019 i need to get message value for parts based on feature id and feature value
so if part id have same featured and feature value of message then i will select message related to feature
create table #message
(
MessageId int,
MessageValue varchar(200),
FeatureId int,
FeatureValue int
)
--drop table #Parts
create table #Parts
(
PartId int,
FeatureId int,
FeatureValue int
)
insert into #message(MessageId,MessageValue,FeatureId,FeatureValue)
values
(1,'30v90v',9012,30),
(1,'30v90v',9015,90),
(2,'50v75v80v',9013,50),
(2,'50v75v80v',9017,75),
(2,'50v75v80v',9018,80),
(3,'20v25v',9013,20),
(3,'20v25v',9017,25),
(4,'301v890v',9013,890),
(4,'301v890v',9017,301)
insert into #Parts(Partid,FeatureId,FeatureValue)
values
(127891,9012,30),
(127891,9015,90),
(904123,9013,50),
(904123,9017,75),
(904123,9018,80),
(32901,9013,20),
(32901,9017,99),
(890215,9013,890)expected result
-
Just to be sure, what you're asking for is PartIDs that have the exact same FeatureID's and FeatureValue's as a given MessageID, correct?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... it took me a minute to figure out what you were asking for and I'll assume that what I stated above is correct.
The following code will do the trick.
--===== This method is the most accurate but only works in 2017 or better because of STRING_AGG().
-- The CONCAT_WS() can be replace with CONCAT and a delimiter added between the columns.
WITH
ctePart AS
(--==== Get the full list of FeatureID's and FeatureValue's for each PartID
SELECT PartID
,FeatureList = STRING_AGG(CONCAT_WS(',',FeatureId,FeatureValue),',')
WITHIN GROUP (ORDER BY FeatureId,FeatureValue)
FROM #Parts
GROUP BY PartID
)
,cteMessage AS
(--=====Get the full list of FeatureID's and FeatureValue's for each MessageValue
SELECT MessageValue = MAX(MessageValue)
,FeatureList = STRING_AGG(CONCAT_WS(',',FeatureId,FeatureValue),',')
WITHIN GROUP (ORDER BY FeatureId,FeatureValue)
FROM #Message
GROUP BY MessageId
)--==== Determining "full" matches is now easy
SELECT p.PartId, m.MessageValue
FROM ctePart p
JOIN cteMessage m
ON m.FeatureList = p.FeatureList
ORDER BY PartID
;The results are as you asked...
If you have to do a whole lot of these on the fly, I'd create a trigger on each table to maintain a "FeatureList" table for both tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Here's a higher risk method of doing the same thing. It's higher in risk because it uses CheckSums instead of the "full monty" like we did with the String_Agg() thing. I'm posting it so that you know it's higher risk if someone suggests it. Back before String_Agg, I'd find my way to do my own string aggregation for this type of thing.
--===== Here's another way to do it with a higher risk of collisions but it does
-- work with less than 2017. It's probably better to use FOR XML PATH because
-- that won't use a CHECKSUM.
WITH
ctePart AS
(--==== Get the full list of FeatureID's and FeatureValue's for each PartID
SELECT PartID
,CSA_FeatureId = CHECKSUM_AGG(FeatureId)
,CSA_FeatureValue = CHECKSUM_AGG(FeatureValue)
FROM #Parts
GROUP BY PartID
)
,cteMessage AS
(--=====Get the full list of FeatureID's and FeatureValue's for each MessageValue
SELECT MessageValue = MAX(MessageValue)
,CSA_FeatureId = CHECKSUM_AGG(FeatureId)
,CSA_FeatureValue = CHECKSUM_AGG(FeatureValue)
FROM #Message
GROUP BY MessageId
)--==== Determining "full" matches is now easy
SELECT p.PartId, m.MessageValue
FROM ctePart p
JOIN cteMessage m
ON m.CSA_FeatureId = p.CSA_FeatureId
AND m.CSA_FeatureValue = p.CSA_FeatureValue
ORDER BY PartID
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As a bit of a sidebar, your #Message table shouldn't exist in a normalized database. The MessageValue appears to be an aggregation of voltages related to the combination of the FeatureID and FeatureValue. It's possible for the those underlying values to change without the MessageValue being changed unless you happen to have an "Enforcement Trigger". It would also be easier to just generate that message on the fly using the appropriate joins with the Parts table.
Think... "By the key, the whole key, and nothing but the key, so help me Codd".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I prefere using check sum
my case have numbers only on feature id and feature value
also i working on sql server 2019
so are using CHECKSUM_AGG will working
or not working good
- ahmed_elbarbary.2010 wrote:
also i working on sql server 2019
so are using CHECKSUM_AGG will working
or not working good
I don't understand what you're saying here. Is the STRING_AGG() solution working for you but not performing well or ???
And, I knew I shouldn't have posted the CHECKSUM_AGG() solution even as an example of what to watch out for. CHECKSUM and CHECKSUM_AGG() aren't even as good as MD5 HASHBYTES. If you use CHECKSUM_AGG() for this, you'll probably come back sometime in the near future asking for a fix for the collisions. Hopefully, you find them before they find you. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply