Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Help Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
Hi All

I have 2 tables.

Table 1 MasterDefects

Field1: Department
Field2: Process
Field3: Type
Field4: Defect

Table 2 Results
Field1: Department
Field2: Process
Field3: Type
Field4: Defect
Field5: ShiftID
Field6: SizeID
Field7: Amount
Field8: ShiftBox


If MasterDefects has 7 rows

Department:::::Process:::::Type:::::::::::Defect
Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot
Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket
Timber::::::::::CrossCut::::Wood Fault::::Large Knot
Timber::::::::::CrossCut::::Wood Fault::::Wet Timber
Timber::::::::::CrossCut::::Wood Fault::::Wood Rott
Timber::::::::::CrossCut::::Wood Fault::::Blue Stain
Timber::::::::::CrossCut::::Wood Fault::::Chipped Off

And my Results had

Department:::::Process:::::Type:::::::::::Defect::::::::::::ShiftID:::::SizeID:::Amount:::ShiftBox
Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Large Knot::::::::1ae::::::::1des:::::1::::::::::RTO1


How can I combine the 2 table in a query to get

Department:::::Process:::::Type:::::::::::Defect::::::::::::ShiftID:::::SizeID:::Amount:::ShiftBox
Timber::::::::::CrossCut::::Wood Fault::::Fallen Out Knot:::1ae::::::::1des:::::3::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Resin Pocket::::::1ae::::::::1des:::::2::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Large Knot::::::::1ae::::::::1des:::::1::::::::::RTO1
Timber::::::::::CrossCut::::Wood Fault::::Wet Timber:::::::Null:::::::::Null::::::Null:::::::Null
Timber::::::::::CrossCut::::Wood Fault::::Wood Rott::::::::Null:::::::::Null::::::Null:::::::Null
Timber::::::::::CrossCut::::Wood Fault::::Blue Stain:::::::::Null:::::::::Null::::::Null:::::::Null
Timber::::::::::CrossCut::::Wood Fault::::Chipped Off:::::::Null:::::::::Null::::::Null:::::::Null

I have tried left and right Inner and Outer Joins but I just cant get the query to show the above.

Cheers

DJ
Post #1364765
Posted Wednesday, September 26, 2012 9:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:05 AM
Points: 320, Visits: 1,095
Try this:


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.ShiftBox
FROM MasterDefects M
LEFT OUTER JOIN Results R
ON M.Department=R.Department
AND M.Process= R.Process
AND M.Type=R.Type
AND M.Defect=R.Defect
GROUP BY
M.Department,
M.Process,
M.Type,
M.Defect,
R.ShiftID,
R.SizeID,
R.ShiftBox;

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!
Post #1364776
Posted Wednesday, September 26, 2012 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
Hi

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.

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.

Cheers

DJ
Post #1364795
Posted Wednesday, September 26, 2012 10:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 4,049, Visits: 9,211
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.


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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364810
Posted Thursday, September 27, 2012 1:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 11:50 AM
Points: 21, Visits: 70
Hi Laurie

Spent 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.
Post #1365063
Posted Thursday, September 27, 2012 2:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:05 AM
Points: 320, Visits: 1,095
No problem
Post #1365085
Posted Thursday, September 27, 2012 10:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 1,948, Visits: 3,184
I have 2 tables.


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 codes

SELECT 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1365371
Posted Friday, September 28, 2012 7:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Ok. 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.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365871
Posted Saturday, September 29, 2012 5:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 1,948, Visits: 3,184
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.


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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366220
Posted Sunday, September 30, 2012 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
CELKO (9/29/2012)
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.


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.


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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1366267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse