SQLTeam.com | Weblogs | Forums

T-sql 2012 self joins


#1

In a sql server 2012 database, I have the following table that I need to obtain data from by doing a lot of self joins.
CREATE TABLE [dbo].[CustomCustomer](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[attributeID] [int] NOT NULL,
[value] varchar NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
GO
LTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_Person] FOREIGN KEY([personID])
REFERENCES [dbo].[Person] ([personID])
GO
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_Person]
GO
ALTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_CustomerAttribute] FOREIGN KEY([attributeID])
REFERENCES [dbo].[CustomerAttribute] ([attributeID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_CustomerAttribute]
GO

I am posting this t-sql question since I would like to know the best way to obtain the required data I need from this table. Basically
data is obtained from this table by looking for the unique values in the attributeID column for each customer that is identified by personID.
The values are unique for a 'group' of atrribute values by looking at the date column of this table.

Customers can have unique categories like location, business type, inventory, and quantity on hand. When data is entered into the group of attribute categories the date
field is used to uniquely identify the group of values that were entered.

Basically I need to obtain data for each customer by the groupings of location (attribute values between 100 and 150) ,inventory (attribute values between 200 and 250) and quantity on
hand (attribute values between 900 and 915). I need to obtain the values from Oct 1. 2015 to Dec. 31 2015.

Thus can you show me the t-sql on how to accomplish this goal by using self-joins and/or whatever you suggest to be used?


#2

Some sample data and the expected output would go a long way towards clarifying the problem. I don't think that I have a handle on what you are trying to accomplish. Can you elaborate?


#3

In a sql server 2012 database, I have the following table that I need to obtain data from by doing a lot of self joins. <<

This is an EAV design and not a valid RDBMS. Please read:

I worked a company that wrote crap code like this back 2000. They wanted super-generic packages that could be change on the fly. Trust me, this does not work in the not-so-long run (2-3 years). It is too generic and the Law of Identity catches up with you. Remember that from your freshman logic course? “To be is to be something in particular; to nothing in particular or everything in general is to be nothing at all!”

Use a NoSQL data base if you wish to give up ACID.

Also, this “Automobiles, Squids and Lady Gaga” has other errors, based on ISO-11179 rules and fundamental of data modeling.

Table names are always plurals because they represent sense, not single instances. Your custom customer is both singular and includes data type in the name

The use of identity is fundamentally wrong. This is a count of physical insertion attempts on one machine, to one table. It has nothing to do with the logical data model

Person ID is one on several counts. Identifiers are never numerics; we do know math on the. Again, it is too generic; what role does the person play in the data model?

Attribute ID again should not be numeric and it is meta data. Remember the 1st rule of data modeling? Never mix data and metadata in the table. Value is both too generic and a reserved word in ISO standard SQL.

Date is a reserved word in SQL it is all as well is too vague (date of what?).

We do not use GUID. My former employee was in love with using them to fake pointer chains in SQL. Unfortunately nobody ever wrote a garbage collection routine like the ones we had in the old network databases. The G in GUID means global, not local.

District ID should not be a numeric because you do know math on it.

I would like to know the best way to obtain the required data I need from this table. <<

There is no good way to do this with EAV. The company I worked for failed, and exists as a “patent troll” today. Can you update your resume? :slightly_smiling: