Joining a table on itself

October 3, 2008

select  RTRIM(LTRIM(rl.ric)) 'Identifier',
        case
            when (rl.code like '%46%' ) THEN 'True'
            when (rl.code like '%47%' ) THEN 'True'
            ELSE 'False' END D1,
        case
            when (rl.hard_restriction = 1 AND rl.hard_to_borrow=0) THEN 'Restricted'
            else '' end 'EventType'
from restricted_list rl_1
    inner join (select id, max(entry_date) as MaxEntryDate
                from restricted_list rl_2
                group by ric) rl_2
        on  rl_1.ric = rl_2.ric
        and rl_1.entry_date = rl_2.MaxEntryDate
order by rl_1.ric
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: