Query Help

Question

Post reply

Query Help

Douglasjbell

SSC Veteran

Points: 289
More actions
September 26, 2012 at 9:02 am

#264915

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

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', '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! 😀

Douglasjbell SSC Veteran Points: 289 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Douglasjbell SSC Veteran Points: 289 More actions · Answer 4

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.

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 5

laurie-789651

SSCertifiable

Points: 7680

September 27, 2012 at 2:09 am

#1542741

No problem:-)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)