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

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: Monday, July 20, 2015 9:35 AM
Points: 393, Visits: 1,224
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 5,352, Visits: 12,095
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.
General Disclaimer:
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: Monday, July 20, 2015 9:35 AM
Points: 393, Visits: 1,224
No problem
Post #1365085
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 @ 3:46 PM
Points: 38,354, Visits: 35,276
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 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 @ 3:46 PM
Points: 38,354, Visits: 35,276
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

Permissions Expand / Collapse