Query help

This group is always awesome. Thanks in advance for your help. I need to
put together some recordsets for a charting application. I think this is
everything (ddl, data, expected result set.) Thanks again.
CREATE TABLE [dbo].[Tracking] (
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
insert into tracking VALUES ('LOCATION','Bedroom')
insert into tracking VALUES ('LOCATION','Dining Room')
insert into tracking VALUES ('LOCATION','Bathroom')
insert into tracking VALUES ('LOCATION','courtyard')
insert into tracking VALUES ('EVENT','verbal aggression')
insert into tracking VALUES ('EVENT','peer')
insert into tracking VALUES ('EVENT','bad behavior')
insert into tracking VALUES ('EVENT','other')
CREATE TABLE [dbo].[Tracking_DATA] (
[ID_] [int] IDENTITY (1, 1) NOT NULL ,
[bts_ID_] [int] NULL ,
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into tracking_DATA VALUES (1, 'LOCATION', 'Bedroom')
insert into tracking_DATA VALUES (1, 'EVENT', 'other')
insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
insert into tracking_DATA VALUES (2, 'LOCATION', 'Bedroom')
insert into tracking_DATA VALUES (2, 'EVENT', 'other')
insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
insert into tracking_DATA VALUES (3, 'LOCATION', 'courtyard')
insert into tracking_DATA VALUES (3, 'EVENT', 'other')
insert into tracking_DATA VALUES (3, 'EVENT', 'peer')
Ideally, the result set of the query would be
Location Event count (headings if possible)
Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1
Also, if possible, another query would return this result set. (I think I
know how to do this one.)
Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1Sorry, im stuck in your DDL, could you explain briefly what these tables are
for ?
I cant see the connection between the estimated result and the source data.
Jens SUessmeyer.
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:ACqee.514$2J6.319@.lakeread06...
> This group is always awesome. Thanks in advance for your help. I need to
> put together some recordsets for a charting application. I think this is
> everything (ddl, data, expected result set.) Thanks again.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('LOCATION','Bedroom')
> insert into tracking VALUES ('LOCATION','Dining Room')
> insert into tracking VALUES ('LOCATION','Bathroom')
> insert into tracking VALUES ('LOCATION','courtyard')
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'LOCATION', 'Bedroom')
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (2, 'LOCATION', 'Bedroom')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (3, 'LOCATION', 'courtyard')
> insert into tracking_DATA VALUES (3, 'EVENT', 'other')
> insert into tracking_DATA VALUES (3, 'EVENT', 'peer')
> Ideally, the result set of the query would be
> Location Event count (headings if possible)
> Bedroom verbal aggression 1
> Bedroom peer 0
> Bedroom bad behavior 0
> Bedroom other 2
> Dining Room verbal aggression 0
> Dining Room peer 0
> Dining Room bad behavior 0
> Dining Room other 0
> Bathroom verbal aggression 0
> Bathroom peer 0
> Bathroom bad behavior 0
> Bathroom other 0
> courtyard verbal aggression 0
> courtyard peer 1
> courtyard bad behavior 0
> courtyard other 1
> Also, if possible, another query would return this result set. (I think I
> know how to do this one.)
> Location Event count
> Bedroom verbal aggression 1
> Bedroom other 2
> courtyard peer 1
> courtyard other 1
>
>|||Sorry, im stuck in your DDL, could you explain briefly what these tables are
for ?
I cant see the connection between the estimated result and the source data.
Jens SUessmeyer.
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:ACqee.514$2J6.319@.lakeread06...
> This group is always awesome. Thanks in advance for your help. I need to
> put together some recordsets for a charting application. I think this is
> everything (ddl, data, expected result set.) Thanks again.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('LOCATION','Bedroom')
> insert into tracking VALUES ('LOCATION','Dining Room')
> insert into tracking VALUES ('LOCATION','Bathroom')
> insert into tracking VALUES ('LOCATION','courtyard')
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'LOCATION', 'Bedroom')
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (2, 'LOCATION', 'Bedroom')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (3, 'LOCATION', 'courtyard')
> insert into tracking_DATA VALUES (3, 'EVENT', 'other')
> insert into tracking_DATA VALUES (3, 'EVENT', 'peer')
> Ideally, the result set of the query would be
> Location Event count (headings if possible)
> Bedroom verbal aggression 1
> Bedroom peer 0
> Bedroom bad behavior 0
> Bedroom other 2
> Dining Room verbal aggression 0
> Dining Room peer 0
> Dining Room bad behavior 0
> Dining Room other 0
> Bathroom verbal aggression 0
> Bathroom peer 0
> Bathroom bad behavior 0
> Bathroom other 0
> courtyard verbal aggression 0
> courtyard peer 1
> courtyard bad behavior 0
> courtyard other 1
> Also, if possible, another query would return this result set. (I think I
> know how to do this one.)
> Location Event count
> Bedroom verbal aggression 1
> Bedroom other 2
> courtyard peer 1
> courtyard other 1
>
>|||Thanks for helping me.
Basically, a script uses the 'tracking' table to build a web page. Each
key_ value is used to create a separate section of the form. The value_ is
used to create a checkbox that the user can select. The 'tracking_Data'
table contains the values that the user selected. I am using all of this
data to build a result set that will be used for a graphing tool.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23vt8ObYUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Sorry, im stuck in your DDL, could you explain briefly what these tables
> are for ?
> I cant see the connection between the estimated result and the source
> data.
> Jens SUessmeyer.
> "Jack" <jack@.jack.net> schrieb im Newsbeitrag
> news:ACqee.514$2J6.319@.lakeread06...
>|||This is a serioius heinous data structure which is why querying for the data
you
want is so difficult.
Here's a solution:
Select Locations.LocationValue, Events.EventValue
, (
Select Count(*)
From (
Select LocationData.LocationValue, EventData.EventValue
From (
Select TD1.Bts_id, TD1.[value] As LocationValue
From dbo.Tracking_Data As TD1
Where TD1.[key] = 'LOCATION'
) As LocationData
Join (
Select TD2.Bts_id, TD2.[value] As EventValue
From dbo.Tracking_Data As TD2
Where TD2.[key] = 'EVENT'
) As EventData
On LocationData.Bts_Id = EventData.Bts_Id
) As CollatedEventData
Where CollatedEventData.LocationValue = Locations.LocationValue
And CollatedEventData.EventValue = Events.EventValue
)
From (
Select T1.[value] As LocationValue
From dbo.Tracking As T1
Where T1.[key] = 'LOCATION'
) As Locations
, (
Select T2.[value] As EventValue
From dbo.Tracking As T2
Where T2.[key] = 'EVENT'
) As Events
Order By Locations.LocationValue, Events.EventValue
Let me make a few suggestions:
1. The reason that I had to use so many subqueries is that those entities ar
e
all lopped into the same table. There should be separate tables for Location
s
and Events.
2. The column names are seriously painful. Beyond the fact that I personally
hate underscores in column names, using underscores at the end of the column
name is really non-intuitive. I removed them for my example and came across
the
next column name faux pas. Don't use "key" and "value" for column names. It
means that the developer *has* surround the column name with square brackets
for
everything which is a serious pain.
HTH
Thomas|||Whew, Hats off for your query, i sure dont know if that works, but reading
the structure of the tables also gave me the creeps.
@.OriginalPoster: Perhaps you have the possibility to redesign your database
structure. as time goes by you will run in more than one issue with this
DB-structure. Yo if you have the chance and the power to makes some design
chances this would make life easier to you in the future. (...just my two
pence...)
Jens Suessmeyer.
"Thomas Coleman" <replyingroup@.anywhere.com> schrieb im Newsbeitrag
news:e5qhKGZUFHA.2520@.TK2MSFTNGP09.phx.gbl...
> This is a serioius heinous data structure which is why querying for the
> data you want is so difficult.
> Here's a solution:
> Select Locations.LocationValue, Events.EventValue
> , (
> Select Count(*)
> From (
> Select LocationData.LocationValue, EventData.EventValue
> From (
> Select TD1.Bts_id, TD1.[value] As LocationValue
> From dbo.Tracking_Data As TD1
> Where TD1.[key] = 'LOCATION'
> ) As LocationData
> Join (
> Select TD2.Bts_id, TD2.[value] As EventValue
> From dbo.Tracking_Data As TD2
> Where TD2.[key] = 'EVENT'
> ) As EventData
> On LocationData.Bts_Id = EventData.Bts_Id
> ) As CollatedEventData
> Where CollatedEventData.LocationValue = Locations.LocationValue
> And CollatedEventData.EventValue = Events.EventValue
> )
> From (
> Select T1.[value] As LocationValue
> From dbo.Tracking As T1
> Where T1.[key] = 'LOCATION'
> ) As Locations
> , (
> Select T2.[value] As EventValue
> From dbo.Tracking As T2
> Where T2.[key] = 'EVENT'
> ) As Events
> Order By Locations.LocationValue, Events.EventValue
> Let me make a few suggestions:
> 1. The reason that I had to use so many subqueries is that those entities
> are all lopped into the same table. There should be separate tables for
> Locations and Events.
> 2. The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the end
> of the column name is really non-intuitive. I removed them for my example
> and came across the next column name faux pas. Don't use "key" and "value"
> for column names. It means that the developer *has* surround the column
> name with square brackets for everything which is a serious pain.
>
> HTH
>
> Thomas
>|||Thanks for looking at this.
You wrote :
>The reason that I had to use so many subqueries is that those entities are
all lopped into the same table. There should be separate tables for
Locations
> and Events.
I agree with you, but the challenge I am having is I have to make this
application generic. The 'Locations' and 'Events' table may be called
something different for another customer. Plus, we will not know how many
different items that will get tracked. In my example, there are only two,
but in the real world, there could be thirty. That is why I have everything
in one table keyed by 'key_' (which I agree with you that the name should
change.)
I definitely need help with data structure stuff, so your suggestions are
welcome and appreciated to make this work.
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:e5qhKGZUFHA.2520@.TK2MSFTNGP09.phx.gbl...
> This is a serioius heinous data structure which is why querying for the
> data you want is so difficult.
> Here's a solution:
> Select Locations.LocationValue, Events.EventValue
> , (
> Select Count(*)
> From (
> Select LocationData.LocationValue, EventData.EventValue
> From (
> Select TD1.Bts_id, TD1.[value] As LocationValue
> From dbo.Tracking_Data As TD1
> Where TD1.[key] = 'LOCATION'
> ) As LocationData
> Join (
> Select TD2.Bts_id, TD2.[value] As EventValue
> From dbo.Tracking_Data As TD2
> Where TD2.[key] = 'EVENT'
> ) As EventData
> On LocationData.Bts_Id = EventData.Bts_Id
> ) As CollatedEventData
> Where CollatedEventData.LocationValue = Locations.LocationValue
> And CollatedEventData.EventValue = Events.EventValue
> )
> From (
> Select T1.[value] As LocationValue
> From dbo.Tracking As T1
> Where T1.[key] = 'LOCATION'
> ) As Locations
> , (
> Select T2.[value] As EventValue
> From dbo.Tracking As T2
> Where T2.[key] = 'EVENT'
> ) As Events
> Order By Locations.LocationValue, Events.EventValue
> Let me make a few suggestions:
> 1. The reason that I had to use so many subqueries is that those entities
> are all lopped into the same table. There should be separate tables for
> Locations and Events.
> 2. The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the end
> of the column name is really non-intuitive. I removed them for my example
> and came across the next column name faux pas. Don't use "key" and "value"
> for column names. It means that the developer *has* surround the column
> name with square brackets for everything which is a serious pain.
>
> HTH
>
> Thomas
>|||There is such a thing as "too" generic. There has to be some structure or
everything becomes nothing more than a couple of tables called "things". The
real key (no pun intended) is commonality. Is there a pattern to the data th
at
they want to store? It may not be possible to create one structure to rule t
hem
all and in the darkness bind them.
Thomas
"Jack" <jack@.jack.net> wrote in message news:DLsee.522$2J6.105@.lakeread06...
> Thanks for looking at this.
> You wrote :
> all lopped into the same table. There should be separate tables for Locati
ons
> I agree with you, but the challenge I am having is I have to make this
> application generic. The 'Locations' and 'Events' table may be called
> something different for another customer. Plus, we will not know how man
y
> different items that will get tracked. In my example, there are only two
,
> but in the real world, there could be thirty. That is why I have everythi
ng
> in one table keyed by 'key_' (which I agree with you that the name should
> change.)
> I definitely need help with data structure stuff, so your suggestions are
> welcome and appreciated to make this work.
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:e5qhKGZUFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||>> the challenge I am having is I have to make this application
generic. <<
ARRRRGHH!! There is no such thing in RDBMS. You have missed all the
foundations. The idea is that you have a known data model of an
external reality that you manipulate. Users do not get to make
elephants fall out of the sky.
for another customer. <<
Then yuou would them appropriately, not generically!!
tracked. <<
Then you have no done your job as a data modeler, have you?
agree with you that the name should change.) <
"To be is to be something in particular; to be nothing in particular is
to be nothing." --Aristole
The name of this design flaw is EAV (entity-attribute-value). It falls
apart in a year in production work two ways:
1) The queries look like the nightmare that Mr. Coleman posted only
*orders of magnitude* -- did you hear that' repeat *orders of
magnitude* -- more complex and slower. The overhead of type conversion
alone will kill a real application. Or did you write an entire
temporal and arithmetic library for VARCHAR(255) strings that does not
use CAST()?
2) All data integrity is destroyed. Any typo becomes a new attribute
or entity. Entities are found missing attributes, so all the reports
are wrong.
3) Try to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order system
when I tried it as an exercise.
4) Try to write a single DEFAULT clause for 30+ entities crammed into
one column. Impossible!
5) Try to set up DRI actions among the entities. If you thought the
WHEN clauses in the single CASE expression were unmaintainable, wait
until you see the "TRIGGERs from Hell" -- Too bad that they might not
fit into older SQL Server which had some size limits. Now maintain it.
I bill $1000-$2000 per day to look at and fix this exact design
problem. Let me know if your boss wants to pay me, fix the problem in
shop or go out of business. It is that kind of mistake.sql