InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:27:43 GMT20http://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspx[quote]I have to admit, that's one numbering system where that would actually work quite effectively because there's virtually no overlap of subcategories (hierarchical level) that you might want to search on.[/quote]That is deliberate. What they fight about is moving and adding new classes. Logic got moved from Philosophy to Math in the modern world. Currently, Creationism is be being put under pseudo-science and the loonies are fighting it. (http://curricublog.wordpress.com/2009/01/01/0019/)http://sensuouscurmudgeon.wordpress.com/2008/12/19/floridas-ronda-storms-much-more-than-creationism/Sun, 30 Sep 2012 12:38:57 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspx[quote][b]CELKO (9/29/2012)[/b][hr][quote]Now I'm surprised at you, Joe. The first thing I'd do to a Dewey Decimal system is to convert it to Nested Sets so you can more easily query it.[/quote]LOL! There is not much easier than WHERE ddc LIKE '51_.%' for finding math books in one table. This is structure (Dewey) versus relations (org charts) -- maybe there is an article in this.[/quote]I have to admit, that's one numbering system where that would actually work quite effectively because there's virtually no overlap of subcategories (hierarchical level) that you might want to search on.Sun, 30 Sep 2012 09:57:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspx[quote]Now I'm surprised at you, Joe. The first thing I'd do to a Dewey Decimal system is to convert it to Nested Sets so you can more easily query it.[/quote]LOL! There is not much easier than WHERE ddc LIKE '51_.%' for finding math books in one table. This is structure (Dewey) versus relations (org charts) -- maybe there is an article in this.Sat, 29 Sep 2012 17:37:58 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxOk. Now I'm surprised at you, Joe. The first thing I'd do to a Dewey Decimal system is to convert it to Nested Sets so you can more easily query it.Fri, 28 Sep 2012 07:38:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspx[quote] I have 2 tables. [/quote]Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you don't). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. It is obvious you have never done a data model before, so let's go thru it slowly. The term “master” was used in tape files and early non-RDBMS systems; it is not part of RDBMS terminology. Likewise, tape file records have fields, but tables have columns; this is not terminology, but fundamental concepts. The four columns you have should be combined into a single defect code. Have you been to a library and seen the Dewey Decimal Classification? This is a hierarchical encoding system, but there are also vector Here is a clean up of what you have now. Notice the “<attribute>_<attribute property>” syntax for data element names: CREATE TABLE Defects(department_name CHAR(10) NOT NULL, process_name CHAR(10) NOT NULL, defect_type CHAR(10) NOT NULL, defect_code CHAR(10) NOT NULL, PRIMARY KEY (department_name, process_name, defect_type, defect_code));CREATE TABLE Defects(defect_code CHAR(10) NOT NULL PRIMARY KEY CHECK (defect-code LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9]'), defect_description VARCHAR(100) NOT NULL);where the defect code is a hierarchy with those components. I have no idea or the specs from which to design that decoding. I gave a dummy to have something to talk about. There is no such thing as a “size_id”; look at your shoes, they have a “shoe_size” because size is a measurement on some scale of the dimension of something in particular, not an entity with an identifier. Amount is usually a dollar figure, but yours look like a count CREATE TABLE Defect_Reports(defect_code CHAR(10) NOT NULL REFERENCES Defects(defect_code) ON UPDATE CASCADE, shift_id CHAR(5) NOT NULL CHECK (??), foobar_size CHAR(3) NOT NULL, defect_cnt SMALLINT DEFAULT 1 NOT NULL, shiftbox_nbr CHAR(5) NOT NULL, PRIMARY KEY (defect_code, foobar_size, shiftbox_nbr));It looks like you want a LEFT OUTER JOIN to preserve the defect codesSELECT D.defect_code, R.foobar_size, R.shiftbox_nbr, SUM (R.defect_cnt) AS defect_cnt_tot FROM Defects AS D LEFT OUTER JOIN Defect_Reports AS R ON D.defect_code = R.defect_code GROUP BY D.defect_code, R.foobar_size, R.shiftbox_nbr;You might want to get a copy of THINKING IN SETS and read the parts on encoding schemes. Thu, 27 Sep 2012 10:15:05 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxNo problem:-)Thu, 27 Sep 2012 02:09:23 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxHi LaurieSpent a bit of time on this, this morning. I couldnt figure out why your example worked but my query didnt. Looked through all the data in my tables and realised that there was a space at the end of my process in my results. So I changed my querey slightly to do a RTRIM on the end of the process field.Everything works perfectly, thanks for the help.Thu, 27 Sep 2012 01:22:56 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspx[quote]Both tables are far to large as this is a running system to give a copy, I am just trying to create a query for a report.[/quote]That's why you're asked only for sample data;-). You did posted sample data but Laurie had to code the DDL and the INSERTs to recreate it.As we're all volunteers, we ask you to provide us with the data in this format so we can concentrate in your problem.:-)Read the article linked in my signature for a better explanation.Wed, 26 Sep 2012 10:01:20 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxHiBoth tables are far to large as this is a running system to give a copy, I am just trying to create a query for a report.Your Select Statement is exactly what I tried, it displays all the information for Masterdefects, includes the columns for results but just gives null values for the result colum set.CheersDJWed, 26 Sep 2012 09:41:21 GMTDouglasjbellhttp://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxTry this:[code="sql"]if object_id('dbo.MasterDefects') is not null drop table dbo.MasterDefects;if object_id('dbo.Results') is not null drop table dbo.Results;create table dbo.MasterDefects(Department Varchar(20), Process Varchar(20), Type Varchar(20), Defect Varchar(20));insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Large Knot' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Wet Timber' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Wood Rott' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Blue Stain' );insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Chipped Off' );create table dbo.Results(Department Varchar(20), Process Varchar(20), Type Varchar(20), Defect Varchar(20),ShiftID Varchar(20),SizeID Varchar(20),Amount int,ShiftBox Varchar(20));insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket', '1ae', '1des', 1, 'RTO1' );insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket', '1ae', '1des', 1, 'RTO1' );insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Large Knot', '1ae', '1des', 1, 'RTO1' );SELECT M.Department, M.Process, M.Type, M.Defect, R.ShiftID, R.SizeID, SUM(R.Amount) as Amount, R.ShiftBoxFROM MasterDefects MLEFT OUTER JOIN Results R ON M.Department=R.Department AND M.Process= R.Process AND M.Type=R.Type AND M.Defect=R.DefectGROUP BY M.Department, M.Process, M.Type, M.Defect, R.ShiftID, R.SizeID, R.ShiftBox;[/code]Edit: Corrected. Results not in the same order - DK if that matters.If you supply test data in a format that can be run, more people will be likely to help! :-DWed, 26 Sep 2012 09:14:16 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1364765-1292-1.aspxHi AllI have 2 tables.Table 1 MasterDefectsField1: DepartmentField2: ProcessField3: TypeField4: DefectTable 2 ResultsField1: DepartmentField2: ProcessField3: TypeField4: DefectField5: ShiftIDField6: SizeIDField7: AmountField8: ShiftBoxIf MasterDefects has 7 rowsDepartment:::::Process:::::Type:::::::::::DefectTimber::::::::::CrossCut::::Wood Fault::::Fallen Out KnotTimber::::::::::CrossCut::::Wood Fault::::Resin PocketTimber::::::::::CrossCut::::Wood Fault::::Large KnotTimber::::::::::CrossCut::::Wood Fault::::Wet TimberTimber::::::::::CrossCut::::Wood Fault::::Wood RottTimber::::::::::CrossCut::::Wood Fault::::Blue StainTimber::::::::::CrossCut::::Wood Fault::::Chipped OffAnd my Results hadDepartment:::::Process:::::Type:::::::::::Defect::::::::::::ShiftID:::::SizeID:::Amount:::ShiftBoxTimber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Large Knot::::::::1ae::::::::1des:::::1::::::::::RTO1How can I combine the 2 table in a query to getDepartment:::::Process:::::Type:::::::::::Defect::::::::::::ShiftID:::::SizeID:::Amount:::ShiftBoxTimber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::3::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::2::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Large Knot::::::::1ae::::::::1des:::::1::::::::::RTO1Timber::::::::::CrossCut::::Wood Fault::::Wet Timber:::::::Null:::::::::Null::::::Null:::::::NullTimber::::::::::CrossCut::::Wood Fault::::Wood Rott::::::::Null:::::::::Null::::::Null:::::::NullTimber::::::::::CrossCut::::Wood Fault::::Blue Stain:::::::::Null:::::::::Null::::::Null:::::::NullTimber::::::::::CrossCut::::Wood Fault::::Chipped Off:::::::Null:::::::::Null::::::Null:::::::NullI have tried left and right Inner and Outer Joins but I just cant get the query to show the above.CheersDJWed, 26 Sep 2012 09:02:29 GMTDouglasjbell