Help with query
in [Programming SQL]
|
Prev: Qs re CTE
Next: Interested in SQL Server Audit Tool
From: SAL on 20 Apr 2010 14:28 Never mind. I didn't post this to get a tongue lashing. "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:881b8b28-65b1-4487-b107-2424359ef8c5(a)t14g2000prm.googlegroups.com... >>> Does this make sense? << > > Better, but the design is still awful. IDENTITY is not relational and > cannot be a key; do you have a real key? or is this a sequential tape > file written with SQL? My first guess was that since you don't know > RDBMS, you would think that a key has to be one column, and did not > make a proper key. But the only possible key is the first three > columns! That makes no sense to me. > > You have more NULL-able columns than major corporation databases; you > don't know ISO-11179 rules for names. SQL programmers do not use BIT > flags -- that was mag tapes and asembly language programming. Even > worse, in SQL Server BIT is an extact numeric that can be {0, 1, > NULL}. > > The data element "TransfersAtAnn" is a verb phrase and not a noun. How > can an attribute be a verb? Did you mean something like > "ann_transfer_flg BIT DEFAULT 0 NOT NULL" ? > > This seems to have to do with real estate. Can I guess that square > footage is never less than zero? But why would it keep changing? If > this is a hisgory, where is the temporal data? > > Here is my best guess: > > CREATE TABLE AffectedEasements > (ann_id INTEGER NOT NULL, -- who or what is ann? > cty_id INTEGER NOT NULL, -- city identifier? > something_squarefootage INTEGER NOT NULL > CHECK (something_squarefootage > 0), > ann_transfer_flg BIT, -- 3-valued bit! non-relational! > -- only possible key! > PRIMARY KEY (ann_id, city_id, something_squarefootage)); > >>>So, for a particular ann_id, give me the rows in which there are no NULL >>>values for ann_transfer_flg and where there is at least one >>>ann_transfer_flg = 1 << > > SELECT DISTINCT ann_id, cty_id > FROM AffectedEasements > WHERE ann_id = @in_ann_id > GROUP BY ann_id, cty_id > HAVING MIN(IFNULL(ann_transfer_flg, -1)) IN (0,1) > AND MAX(IFNULL(ann_transfer_flg, -1))= 1; > > You really need to start over with this schema. It is a textbook bad > example.
From: Tony Rogerson on 20 Apr 2010 14:53 Ignore him SAL - he's a well know arrogant prat who bashes people using Microsoft technologies. He's old and bitter because nobody will employ him anymore because he's so out of date and his knowledge is so narrow to the point its unusable in the real world on real products. Tony. "SAL" <SAL(a)nospam.nospam> wrote in message news:ehjtXdL4KHA.4332(a)TK2MSFTNGP02.phx.gbl... > Never mind. I didn't post this to get a tongue lashing. > > > "--CELKO--" <jcelko212(a)earthlink.net> wrote in message > news:881b8b28-65b1-4487-b107-2424359ef8c5(a)t14g2000prm.googlegroups.com... >>>> Does this make sense? << >> >> Better, but the design is still awful. IDENTITY is not relational and >> cannot be a key; do you have a real key? or is this a sequential tape >> file written with SQL? My first guess was that since you don't know >> RDBMS, you would think that a key has to be one column, and did not >> make a proper key. But the only possible key is the first three >> columns! That makes no sense to me. >> >> You have more NULL-able columns than major corporation databases; you >> don't know ISO-11179 rules for names. SQL programmers do not use BIT >> flags -- that was mag tapes and asembly language programming. Even >> worse, in SQL Server BIT is an extact numeric that can be {0, 1, >> NULL}. >> >> The data element "TransfersAtAnn" is a verb phrase and not a noun. How >> can an attribute be a verb? Did you mean something like >> "ann_transfer_flg BIT DEFAULT 0 NOT NULL" ? >> >> This seems to have to do with real estate. Can I guess that square >> footage is never less than zero? But why would it keep changing? If >> this is a hisgory, where is the temporal data? >> >> Here is my best guess: >> >> CREATE TABLE AffectedEasements >> (ann_id INTEGER NOT NULL, -- who or what is ann? >> cty_id INTEGER NOT NULL, -- city identifier? >> something_squarefootage INTEGER NOT NULL >> CHECK (something_squarefootage > 0), >> ann_transfer_flg BIT, -- 3-valued bit! non-relational! >> -- only possible key! >> PRIMARY KEY (ann_id, city_id, something_squarefootage)); >> >>>>So, for a particular ann_id, give me the rows in which there are no NULL >>>>values for ann_transfer_flg and where there is at least one >>>>ann_transfer_flg = 1 << >> >> SELECT DISTINCT ann_id, cty_id >> FROM AffectedEasements >> WHERE ann_id = @in_ann_id >> GROUP BY ann_id, cty_id >> HAVING MIN(IFNULL(ann_transfer_flg, -1)) IN (0,1) >> AND MAX(IFNULL(ann_transfer_flg, -1))= 1; >> >> You really need to start over with this schema. It is a textbook bad >> example. > >
From: SAL on 20 Apr 2010 15:12 Thanks Tony. It's kind of funny cause my question wasn't about the design of an existing database model. I needed help on a query, which I've subsequently figured out a solution to by using a nested Group by Having Count expression. Dear --CELKO--, If I post something in the future in this forum, please don't bother responding. S "Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message news:OOiGLrL4KHA.1660(a)TK2MSFTNGP04.phx.gbl... > Ignore him SAL - he's a well know arrogant prat who bashes people using > Microsoft technologies. > > He's old and bitter because nobody will employ him anymore because he's so > out of date and his knowledge is so narrow to the point its unusable in > the real world on real products. > > Tony. > > "SAL" <SAL(a)nospam.nospam> wrote in message > news:ehjtXdL4KHA.4332(a)TK2MSFTNGP02.phx.gbl... >> Never mind. I didn't post this to get a tongue lashing. >> >> >> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message >> news:881b8b28-65b1-4487-b107-2424359ef8c5(a)t14g2000prm.googlegroups.com... >>>>> Does this make sense? << >>> >>> Better, but the design is still awful. IDENTITY is not relational and >>> cannot be a key; do you have a real key? or is this a sequential tape >>> file written with SQL? My first guess was that since you don't know >>> RDBMS, you would think that a key has to be one column, and did not >>> make a proper key. But the only possible key is the first three >>> columns! That makes no sense to me. >>> >>> You have more NULL-able columns than major corporation databases; you >>> don't know ISO-11179 rules for names. SQL programmers do not use BIT >>> flags -- that was mag tapes and asembly language programming. Even >>> worse, in SQL Server BIT is an extact numeric that can be {0, 1, >>> NULL}. >>> >>> The data element "TransfersAtAnn" is a verb phrase and not a noun. How >>> can an attribute be a verb? Did you mean something like >>> "ann_transfer_flg BIT DEFAULT 0 NOT NULL" ? >>> >>> This seems to have to do with real estate. Can I guess that square >>> footage is never less than zero? But why would it keep changing? If >>> this is a hisgory, where is the temporal data? >>> >>> Here is my best guess: >>> >>> CREATE TABLE AffectedEasements >>> (ann_id INTEGER NOT NULL, -- who or what is ann? >>> cty_id INTEGER NOT NULL, -- city identifier? >>> something_squarefootage INTEGER NOT NULL >>> CHECK (something_squarefootage > 0), >>> ann_transfer_flg BIT, -- 3-valued bit! non-relational! >>> -- only possible key! >>> PRIMARY KEY (ann_id, city_id, something_squarefootage)); >>> >>>>>So, for a particular ann_id, give me the rows in which there are no >>>>>NULL values for ann_transfer_flg and where there is at least one >>>>>ann_transfer_flg = 1 << >>> >>> SELECT DISTINCT ann_id, cty_id >>> FROM AffectedEasements >>> WHERE ann_id = @in_ann_id >>> GROUP BY ann_id, cty_id >>> HAVING MIN(IFNULL(ann_transfer_flg, -1)) IN (0,1) >>> AND MAX(IFNULL(ann_transfer_flg, -1))= 1; >>> >>> You really need to start over with this schema. It is a textbook bad >>> example. >> >>
From: Tony Rogerson on 20 Apr 2010 15:16 > Better, but the design is still awful. IDENTITY is not relational and > cannot be a key; do you have a real key? or is this a sequential tape In what way are values returned by the IDENTITY property on INSERT non-relational? It's relational, its equivalent to a DEFAULT constraint except the value given is read only and cannot be changed. Your real b1tch here is that it is not ISO SQL - fine, just keep your argument at that and stop trying to pull in relational theory and inaccurately apply it to this situation - also, I think you need to brush up on what First Normal Form is by the way! A unique constraint or index can be added to the column that has the IDENTITY property to enforce uniqueness so referential integrity is fine. By "real key" you probably mean a natural key; that is fine and yes most (but not all) tables have a natural key. A surrogate key is indeed part of the model - even Codd says so in the article we both keep referencing - if you look down he talks about using the surrogate key in joins the fact that it being there must be shown to the user; though Date takes this forward reminding us that because of Codds own information principal the values for the surrogate key must be displayed to the user, I disagree with this - the surrogate key values should only be available within the scope of the application boundaries and NEVER displayed to end users through the UI - I agree with that because unless the database is in itself the central issuer of natural keys then verification is a problem. Now - frankly --CELKO-- enough of your ignorant attitude on this - go and do some flaming research and actually look at what people have written on surrogate keys - also take the advice of product experts (like myself) on what the definition of the IDENTITY property is and how it works under the covers. --ROGGIE--
From: Plamen Ratchev on 20 Apr 2010 17:29
Here is one solution: SELECT AnnId FROM AffectedEasementsGASB GROUP BY AnnId HAVING MAX(CAST(TransfersAtAnn AS INT)) = 1 AND COUNT(TransfersAtAnn) = COUNT(*); -- Plamen Ratchev http://www.SQLStudio.com |