giving one union preference

[eteunisse]

Let say I have a query with the following structure:

Select name, mdate, kdate
from table1 a
inner join (
select name, mdate
from table2 b
where a.id = b.id
and mdate >= kdate
union
select name, mdate
from table3 c
where a.id = c.id
and mdate >= kdate
) mindate

When the select mdate from the inner join union (mindate) returns the
same mdate for table b and c, I want to give preference to the c.mdate
and so c.name.

Is there a way to do this?

thanks!

regards,
Erwin

[Alejandro Mesa]

eteunisse,

Can you expand more on this and if possible post some DDL, sample data and
expected result?

The derive table is being built using a UNION operator, so if both rows (b
and c) have same ([name], [mdate]) then just one row will result. why give a
preference if they are equal?

Also, use table alias to qualify columns in your derived table. SQL Server
will use the outer ones instead. Are you aware of the "cross join" operation?

Select mindate.name, mindate.mdate, a.kdate

from table1 a
inner join (

select b.name, b.mdate

from table2 b
where a.id = b.id

and b.mdate >= a.kdate
union
select c.name, c.mdate

from table3 c
where a.id = c.id

and c.mdate >= a.kdate
) mindate

AMB

[eteunisse]

If the dates from the union are equal, the name does not have to be
and I want to use a specific name.

The shown query is a very simplified version of my query.
In the query shown underneath the last inner join selects the
min(date) from the union. When two dates are equal I need the red_fct
from the table DWH_TB_GEG_INSTRUMENT_EARLY_REDEMPTION.

select distinct
DWH_TB_GEG_INSTRUMENT_MASTER.instrument_ODS_ID as
instrument_ODS_ID,
duration_type_cde,
duration_dte,
red_fct,
DWH_TB_GEG_PORTFOLIO_POSITION.position_NAV_dte,
DWH_TB_GEG_INSTRUMENT_MASTER.interest_rate_pct/100 as
interest_rate_pct,
DWH_TB_GEG_INSTRUMENT_DETAIL.instrument_price_euro_amt as
instrument_price_euro_amt
from DWH_TB_GEG_INSTRUMENT_MASTER
inner join DWH_TB_GEG_PORTFOLIO_POSITION on
DWH_TB_GEG_INSTRUMENT_MASTER.instrument_ODS_ID =
DWH_TB_GEG_PORTFOLIO_POSITION.instrument_ODS_ID
inner join DWH_TB_GEG_INSTRUMENT_DETAIL on
DWH_TB_GEG_INSTRUMENT_MASTER.instrument_ODS_ID =
DWH_TB_GEG_INSTRUMENT_DETAIL.instrument_ODS_ID
inner join DWH_TB_GEG_DAY on
DWH_TB_GEG_PORTFOLIO_POSITION.position_NAV_dte =
DWH_TB_GEG_DAY.day_dte
and DWH_TB_GEG_INSTRUMENT_DETAIL.dte = DWH_TB_GEG_DAY.dte
inner join (
select im.instrument_ods_id, 'I' as duration_type_cde,
ia.INTEREST_DTE as duration_dte, 100.00 as red_fct -- Bij max altijd
uitgaan van 100% redemption
from DWH_TB_GEG_INSTRUMENT_MASTER im
inner join DWH_TB_GEG_INSTRUMENT_INTEREST_ADJUSTMENT ia on
im.instrument_ODS_ID = ia.INSTRUMENT_ID
union
select im.instrument_ods_id, 'I' duration_type_cde,
er.WITHDRAWAL_DTE, er.WITHDRAWAL_FCR as red_fct
from DWH_TB_GEG_INSTRUMENT_MASTER im -- Hier joinen met early
redemption tabel op id en datum voor verkrijgen factor
inner join DWH_TB_GEG_INSTRUMENT_EARLY_REDEMPTION er on
im.instrument_ODS_ID = er.INSTRUMENT_ID
) dt_i on DWH_TB_GEG_INSTRUMENT_MASTER.instrument_ods_id =
dt_i.instrument_ods_id
and dt_i.duration_dte =
(
select min(duration_dte) from
(
select ia2.INTEREST_DTE as duration_dte
from DWH_TB_GEG_INSTRUMENT_MASTER im2
inner join DWH_TB_GEG_INSTRUMENT_INTEREST_ADJUSTMENT ia2 on
im2.instrument_ODS_ID = ia2.INSTRUMENT_ID
where im2.instrument_ods_id = dt_i.instrument_ods_id
and ia2.INTEREST_DTE >=
DWH_TB_GEG_PORTFOLIO_POSITION.position_NAV_dte
union
select er2.WITHDRAWAL_DTE
from DWH_TB_GEG_INSTRUMENT_MASTER im2
inner join DWH_TB_GEG_INSTRUMENT_EARLY_REDEMPTION er2 on
im2.instrument_ODS_ID = er2.INSTRUMENT_ID
where im2.instrument_ods_id = dt_i.instrument_ods_id
and er2.WITHDRAWAL_DTE >=
DWH_TB_GEG_PORTFOLIO_POSITION.position_NAV_dte
) mindate
)

[Plamen Ratchev]

It is not very clear what you need here, but one assumption is that the
value in the name column in table2 and table3 can be different for the same
mdate and you want to select the one from table3 if mdate exists in both
table2 and table3. In that case something like this should do:

***untested***

Select name, mdate, kdate
from table1 a
inner join (
select name, mdate
from table2 b
where a.id = b.id
and mdate >= kdate

and not exists (select *
from table3 d
where a.id = d.id
and d.mdate >= a.kdate
and d.mdate = b.mdate)

union
select name, mdate
from table3 c
where a.id = c.id
and mdate >= kdate
) mindate

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Alejandro Mesa]

eteunisse,

> If the dates from the union are equal, the name does not have to be
> and I want to use a specific name.

You are right, sorry for my confusion.

AMB

[--CELKO--]

>> When the select mdate from the inner join union (mindate) returns the same mdate for table b and c, I want to give preference to the c.mdate and so c.name. <<

This question makes no sense. The poorly named derived table,
Mindate, acts as if it is materialized and you no longer have access
to tables B and C. It is like asking to see the eggs after the cake
has been baked.