Semijoin, sort of. Not really. 1

[travisbrown]

Okay, maybe there is a simple way to do this that I'm overlooking.

I need to join two table, but only return the first record on the right table where there may be many. Sort of a semijoin, I guess, but returning the matching record too.

I've tried a bunch of things, but am not getting very far. Before doing some inelegant ASP scripting, is there a way to do this in SQL? Here's my starting query that returns the one to many records. SCHED is the parent table, SEG is the child.

SELECT 
SCHED.SCHD_ID, 
SEG.START_DTE, 
SCHED.ACT_CPNT_ID, SCHED.SCHD_DESC, 
SCHED.CANCELLED,
SCHED.NOTACTIVE 
FROM PLATEAU.PA_SCHED SCHED  LEFT OUTER JOIN PLATEAU.PA_SCH_SEG SEG ON SEG.SCHD_ID = SCHED.SCHD_ID
WHERE (SEG.START_DTE BETWEEN '01-July-2008' AND '01-August-2008') ORDER BY SEG.START_DTE ASC

 

[jimirvine]

Hi, could you provide a test case i.e. som esample data and expected results. Please post the sample data in the form of create table and insert scripts and make the data as representative as possible.

 

[taupirho]

Well, since we don't know what your data looks like. Try the following

1) Use SELECT DISTINCT - it might just work

otherwise

2) Use analytics to get a row number per desired partition on the SEG table, then an inline view to bring out only row numbers = 1

e.g
Lets say your SEG table had multiple ID's per ID on the SCHED table and you wanted only the one associated with the first start_dte

Then something like this (not tested) should do the trick

SELECT *
from
(
SELECT
SCHED.SCHD_ID,
SEG.START_DTE,
SCHED.ACT_CPNT_ID, SCHED.SCHD_DESC,
SCHED.CANCELLED,
SCHED.NOTACTIVE,
row_number() over (partition by SEG.ID ORDER BY START_DATE) RN
FROM PLATEAU.PA_SCHED SCHED LEFT OUTER JOIN PLATEAU.PA_SCH_SEG SEG ON SEG.SCHD_ID = SCHED.SCHD_ID
WHERE (SEG.START_DTE BETWEEN '01-July-2008' AND '01-August-2008') ORDER BY SEG.START_DTE ASC
)
WHERE RN = 1


If you had multiple ID'S AND START_DTE's on SEG per sched ID then you would change the above analytic line to

row_number() over (partition by SEG.ID ,start_dte ORDER BY START_DATE) RN



In order to understand recursion, you must first understand recursion.

 

[travisbrown]

taupirho,

Setting a row number column for the child records then choosing row one? Simple as that? Really?

Huh. Smart.

I have never seen the row_number() over (partition... construct (I come form a T-SQL world). I'll have to read up on that.

Thanks.

Travis