Self join with inner and outer join query

Question

I have a table that is set up so that one column (attribute) contains information like first name, last name, account number and any other information related to a thing in the database. Another column (attributeType) contains a number indicating what the attribute is e.g. 1 might be first name, 2 last name and 3 account number etc. There is another column (enddate) indicating if the record is current by having a date there. Usually it will be set to the year 9999 when current and some date in the past otherwise. All data describing the same thing has a unique value too in another column (entity) so that each record with the same number in the entity column will be describing the one person. E.g.

entity  attribute  attributetype  enddate
------  ---------  -------------  --------
1       ben        1              9999-1-1
1       alt        2              9999-1-1
1       12345      3              9999-1-1
2       sam        1              9999-1-1
2       smith      2              9999-1-1
2       98765      3              1981-1-1

I want to select a person from the above table with a specific 1st and last name where the name will be current but not output the account number if it is not. Assuming the table is called tblAccount I do the following for the name part:

select ta1.attribute '1st Name', ta2.attribute 'last name'
from tblAccount ta1
inner join tblAccount ta2 on ta1.entity = ta2.entity
where ta1.attribute = 'sam' and ta2.attribute = 'smith'
      and ta1.attributetype = 1 and ta2. attributetype = 2
      and ta1.enddate > getdate() and ta2.enddate > getdate()

and it outputs the first and last names as expected, but when I want to include the account number column I get nothing output:

select ta1.attribute '1st Name', ta2.attribute 'last name', ta3.attribute 'account#'
from tblAccount ta1
inner join tblAccount ta2 on ta1.entity = ta2.entity
left join tblAccount ta3 on ta1.entity = ta3.entity
where ta1.attribute = 'sam' and ta2.attribute = 'smith'
      and ta1.attributetype = 1 and ta2. attributetype = 2
      and ta1.enddate > getdate() and ta2.enddate > getdate()
      and ta3.attributetype = 3 and ta3.enddate > getdate()

What I would like to see is the first and last names output with nothing in the account# column in the above case where it is not current. What am I doing wrong and how can I correct this query?

Y don't u just post what you want as your final result set.. — Praveen Nambiar, Mar 07 '13 at 15:26
If you're entering criteria in the `where` clause for ta3, this may as well be an `inner join`. — Jeff Rosenberg, Mar 07 '13 at 15:28
This type of data structure is a classic SQL anti-pattern called Entity-Attribute-Value, and if you have any control over the database, I'd recommend you don't use it. See http://dba.stackexchange.com/questions/20759/is-there-a-name-for-this-database-structure for more information. — Simon Kingston, Mar 07 '13 at 15:33
@Simon like cursors, dynamic SQL, and functions, [EAV is not the devil](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx) and I'd recommend you don't make blanket statements. — Aaron Bertrand, Mar 07 '13 at 15:36
Try moving the ta3.attributetype = 3 and ta3.enddate > getdate() to the end of the LEFT JOIN clause instead of the WHERE clause. — Simon Kingston, Mar 07 '13 at 15:38
@AaronBertrand Fair enough. I'll amend my statement to say that my experience with this type of structure has been painful and I'd recommend against it unless it's absolutely necessary. — Simon Kingston, Mar 07 '13 at 15:43
@SimonKingston Actually, this might be an exceptional case of where it makes sense to use this way of setting things up. I am not actually dealing with accounts but simplified my problem by representing it that way. We are dealing with many many databases in different places, each one having a common theme and different requirements which could change from year to year. To do it in any regular way would mean to have, say, 50 databases to understand individually instead of one common design. Not that I can claim to be an export on these matters. — Ban Atman, Mar 07 '13 at 16:05

JAQFrost · Accepted Answer · 2013-03-07T17:00:52.643

You have to move the date comparison to the join condition:

select ta1.attribute '1st Name'
    , ta2.attribute 'last name'
    , ta3.attribute 'account#'
from tblAccount ta1
inner join tblAccount ta2 
    on ta1.entity = ta2.entity
     and ta1.attributetype = 1 and ta2. attributetype = 2
     and ta1.enddate > getdate() and ta2.enddate > getdate()
left join tblAccount ta3 on ta1.entity = ta3.entity
      and ta3.attributetype = 3 and ta3.enddate > getdate()
where ta1.attribute = 'sam' and ta2.attribute = 'smith'

When it's in the where clause it's comparing getdate() to NULL if there is no account, which returns NULL. So no record.

EDIT:

In response to the valid concern about multiple valid records, and to make the code a little more maintenance freindly:

DECLARE @FNAME VARCHAR(50) = 'sam'
    , @LNAME VARCHAR(50) = 'smith'
    , @now DATETIME2(7) = GETDATE();

SELECT 
    name.[1st Name]
    , name.[last name]
    , name.entity
    , 
        (
            select 
                top 1 
                ta3.attribute
            FROM tblAccount ta3 
            WHERE 
                ta3.entity = name.entity
                and 
                ta3.attributetype = 3 
                and 
                ta3.enddate > @now
            ORDER BY 
                ta3.enddate 
        )
FROM 
    (        
        select 
            ta1.attribute '1st Name'
            , ta2.attribute 'last name'
            , ta.entity
            , ROW_NUMBER()
                OVER(
                    PARTITION BY 
                        ta1.entity
                    ORDER BY 
                        ta1.enddate
                    ) r
        from 
            tblAccount ta1
        inner join tblAccount ta2 
            on 
            ta1.entity = ta2.entity
            and 
            ta2. attributetype = 2
            and 
            ta2.enddate > @now
            and 
            ta2.attribute = @LNAME
        where 
            ta1.attributetype = 1 
            and 
            ta1.attribute = @fname 
            and 
            ta1.enddate > @now
    ) name
WHERE    
    NAME.r = 1

;

This code works around the implied assumptions of one entity per first/last name and exactly one enddate after the execution time. The variables are a little more stored proc friendly, and allow you to change the "as of" date. And if you're stuck with EAV, you will likely want stored procs. I'm taking the first record ending after the date in question, on the assumption that any later record(s) should only be valid after that one expires. Maybe it's overkill, since it's beyond the scope of the OP's question but it's a valid point.

I say "stuck with EAV". While EAV isn't always bad; neither is shooting someone in the back. In either case, you'd better have solid justification if you expect to get it past a jury. In a NoSQL storage pattern it's fine, but EAV is usually a poor implementation pattern for the RDBMS paradigm.

Though from the OP's later comment, it looks like he's hit on one of the better reasons.

+1 referencing an outer table in the `WHERE` clause turns an outer join into an inner join. — Aaron Bertrand, Mar 07 '13 at 15:37
Thank you very much. That worked. Will hopefully have this straight for the next time I need to do something like this. And thanks @AaronBertrand for elaborating. — Ban Atman, Mar 07 '13 at 15:41
@Jason: what if ther are expired records in the database, which seems the point of this unusual structure? — Pieter Geerkens, Mar 07 '13 at 15:47
Expired records wouldn't come up, which seems to be the intent of the comparison to getdate(). — JAQFrost, Mar 07 '13 at 15:58

Pieter Geerkens · Answer 2 · 2013-03-07T23:23:23.957

Each attribute is actually a distinct entity in this model, but they are all sharing the same storage in the same physical table (Why?). This yields:

with data as (
   select entity = 1, attribute = 'ben',   attributeType=1, enddate = convert(datetime,'99990101') union all
   select entity = 1, attribute = 'alt',   attributeType=2, enddate = convert(datetime,'99990101') union all
   select entity = 1, attribute = '12345', attributeType=3, enddate = convert(datetime,'99990101') union all
   select entity = 2, attribute = 'sam',   attributeType=1, enddate = convert(datetime,'99990101') union all
   select entity = 2, attribute = 'smith', attributeType=2, enddate = convert(datetime,'99990101') union all
   select entity = 2, attribute = '67890', attributeType=3, enddate = convert(datetime,'99990101') union all
   select entity = 2, attribute = '68790', attributeType=3, enddate = convert(datetime,'20130331') union all
   select entity = 2, attribute = '876', attributeType=3, enddate = convert(datetime,'19810101') 
) 
select top 1
    FirstName, LastName, AccountNum
from (
  select top 1 
    a1.entity, FirstName, LastName
  from (
    select entity, enddate, attribute as FirstName
    from data d 
    where d.enddate >= getdate()
      and attributeType = 1
  ) a1
  join (
    select entity, enddate, attribute as LastName
    from data 
    where enddate >= getdate()
      and attributeType = 2
  ) a2 on a1.entity = a2.entity
     and a1.enddate = a2.enddate
  where FirstName = 'sam' and LastName = 'smith'
    and a1.enddate >= getdate() and a2.enddate >= getdate()
  order by a1.enddate
) E
left join (
  select entity, enddate, attribute as AccountNum
  from data 
  where enddate >= getdate()
    and attributeType = 3
) a3 on a3.entity = E.entity
order by a3.enddate

returning:

FirstName LastName AccountNum
--------- -------- ----------
sam       smith    68790

Note that it is quite common for accountign departments, at least, to enter future transactions during quiet times of the month, especially if those transactions will take effect during busy times of the month (ie month-end). The same for annual transactions. One should not assume that only one record can exist with an expiry > getdate().

I like the consideration about multiple valid entries, though your logic won't filter properly. Your subqueries do top 1 by enddate w/o filtering for the proper entity. — JAQFrost, Mar 07 '13 at 16:27
It's not an optimization, it's a logic error. Top one order by enddate is the last entered by attribute type. So you get last type 1, last type 2, last type 3 and hope that they're the same entity who happens to be named sam smith. Otherwise, you return NULL. — JAQFrost, Mar 07 '13 at 16:50
You're still doing top 1 by enddate on the a3 subquery without a filter on the entity. — JAQFrost, Mar 07 '13 at 22:07
I filter on the First/Last-name in E, and join a3 to E on the entity instance that meets all criteria in the E subquery. What more is needed? — Pieter Geerkens, Mar 07 '13 at 22:21
The "top 1" in subquery a3 will return a single arbitrary row of all type 3 rows with the nearest enddate after or equal to the current time. If that single row doesn't apply to the same entity as in E, you show NULL for the Account. — JAQFrost, Mar 07 '13 at 22:36
@Jason: OKAY. I will move the join clause into the a3 sub-query to fix that. — Pieter Geerkens, Mar 07 '13 at 22:40
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/25813/discussion-between-jason-quinones-and-pieter-geerkens) — JAQFrost, Mar 07 '13 at 22:43

Self join with inner and outer join query

2 Answers2