SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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::::Tonguerocess:::::Type::::::::::BigGrinefect
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::::Tonguerocess:::::Type::::::::::BigGrinefect::::::::::::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::::Tonguerocess:::::Type::::::::::BigGrinefect::::::::::::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
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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! :-D
Douglasjbell
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16464 Visits: 19086
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
Douglasjbell
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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.
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
No problem:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85735 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85735 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search