Fill table cells on ROW info
-
Hi,
I have lite experiece with MS SQL SERVER 2014.
I try to write query to get info from order DB.
I have LEFT JOIN on other table with IDs and FNAME and LNAME.
On this table i have like log type. PK_KART is related to same person. I just can't return same values.
At NULL FNAME and LNAME should return same values if in LOG column contain ID prom other table like Receiver:59 and it did but if PK_KART id number the same it returns same value.
-
I don't completely understand what you're going for. Can you post table definitions and the query you're currently using?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
SELECT
PK_KART
,TIME
,LOG
,TEMP1.FNAME
,TEMP1.LNAME
FROM LOG_DB
LEFT JOIN TEMP1 ON (LOG_DB.LOG LIKE ( '%Receiver:' + CONVERT(nvarchar,TEMP1.ID)))
WHERE TIME between '2020-01-01' and '2022-03-01' and PK_KART = '7151'
ORDER BY TIME DESC -
I am so sorry for my bad explanation cuz i am not a programmer.
This is what i got now:
This is result i looking for:
Tryed to do research and found something about finding correlated rows but don't know how to use that.
Now i get NULL in other cells exept in ROW what i can directly compare like LOG - Receive:59 it match.
-
What this is saying is that only one row in TEMP1 matches the join criteria, but three rows in LOG_DB match the WHERE clause. That's exactly how it's supposed to work. You're getting it right. If you want formatting, then you just do that. But there's not really a way to write a query that will return values where the JOIN criteria shows no values matching.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I assumed that the only value after 'Receiver' was the id. If more string can follow, the code below will need changed. I was not able to test the code since you didn't provide directly usable sample data.
;WITH CTE_PK_KART_RECEIVERS AS (
SELECT PK_KART, CAST(SUBSTRING(LOG, CHARINDEX('Receiver:', LOG) + 9, 10) AS int) AS RECEIVER_ID
FROM LOG_DB
WHERE LOG LIKE '%Receiver:%'
)
SELECT
LOG_DB.PK_KART
,LOG_DB.TIME
,LOG_DB.LOG
,TEMP1.FNAME
,TEMP1.LNAME
FROM LOG_DB
INNER JOIN CTE_PK_KART_RECEIVERS CPKR ON CPKR.PK_KART = LOG_DB.PK_KART
LEFT OUTER JOIN TEMP1 ON TEMP1.ID = CPKR.RECEIVER_ID
WHERE LOG_DB.TIME between '2020-01-01' and '2022-03-01' and LOG_DB.PK_KART = '7151'
ORDER BY TIME DESCSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
See the article at the first link in my signature line below. If you were to provide a little readily consumable data as laid out in that article (which is just one method of many for doing so), perhaps we could show you how to do this using LEAD or LAG.
--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