need to link tables on a criteria

[sandeep0000]

sorry i posted this on sql server reporting solutions, not sure which forum is better to post this question

i am doing a report. i have two tables es_sts and INDV_TM_LIM_CLK.

both tables have a cwin field , both are have cwin populated.

that is one link i need to do.

but on the other link, i have cs_id in both tables also.
but sometimes the INDV_TM_LIM_CLK is populated , sometimes it is.

so i want to link on cwin first and then

if my INDV_TM_LIM_CLK ,cs_id is not null then i want that to be my second link. but if the cs_id in INDV_TM_LIM_CLK is null, i only want to link on cwin.

thanks

 

[PHV]

Any chance you could CLEARLY explain what you want to do ?
What is your meaning of a second link ?
Why not posting some input samples, your actual SQL code, your actual results with the posted input and SQl, and finally the expected results ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

yes, it seems quite confusing

allow me to take a wild guess

select ...
  from es_sts as E 
inner
  join INDV_TM_LIM_CLK as I
    on I.cwin = E.cwin
   and (
       I.cs_id = E.cs_id
    or I.cs_id is null
       )

r937.com | rudy.ca

 

[sandeep0000]

well i want to link the two tables on cwin every time.

but there is a second link i want to do on these two tables besides cwin, but this second link will only be sometimes.

there is a cs_id field in both tables, but there are times where cs_id is null in the INDV_TM_LIM_CLK table, in the es_sts table the cs_id is always populated (never null).

so i want to link the two tables by cwin always.
but i want to link by cs_id only if INDV_TM_LIM_CLK.cs_id is not null, but when INDV_TM_LIM_CLK.cs_is is null i only want to link on cwin.

i hope this was a better explanation.

thanks for all your help

 

[r937]

did you try my query?

r937.com | rudy.ca

 

[PHV]

Like this ?
SELECT *
FROM INDV_TM_LIM_CLK I
INNER JOIN es_sts E ON I.cwin = E.cwin
WHERE COALESCE(I.cs_id,E.cs_id)=E.cs_id

FYI, the above should return the same rows as the query suggested by rudy.