Solved

SQL Query to pull two pieces of data from a table with the same link

Posted on 2014-10-13
12
147 Views
Last Modified: 2014-11-12
Hi
On a scale of 1 to 10 (1 being completely new and 10 being an expert), when it comes to T-SQL queries I'm probably a 2 or 3.  I can do basic selects and updates, linking tables, and other primitive functions like LEN, etc.

The problem I'm running into is I'm trying to link to a table that has TWO values I need.  "StartingWeight" and "FinishWeight" of a product.  The link is "OwnerIdent", which links to the actual workorder table "ident".

So for instance:

dbo.Workorders
Ident  
001      
002      
003      
004      

dbo.Weights
OwnerIdent          Attribute          Value
001                          StartWeight     1500
001                          FinishWeight     1520
002                         StartWeight      1592
002                          FinishWeight     1692
003                          StartWeight     1792
003                          FinishWeight     1805
004                          StartWeight     85
004                          FinishWeight     92

I can link the two tables on a query to dbo.workorders like so:

left outer join dbo.weights on dbo.workorders.ident = dbo.weights.Ownerident

but how do I get both the startweight and finishweight into two different columns on the query?  For instance, I want to have:

Workorder       StartWeight        FinishWeight
001                    1500                    1520
002                    1592                    1692
and so forth.

The only thing I can figure out how to do is:

 left outer join dbo.weights on dbo.workorders.ident = dbo.weights.Ownerident and Attribute = 'StartWeight'
...that gets me only the start weight, but I want both

Hope that makes sense

Appreciate any help :)
0
Comment
Question by:Mystical_Ice
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 150 total points
ID: 40378725
here is the wanted SQL

select Workorders, case when b.Attribute  ='StartWeight' then sum(b.value) else 0 end as StartWeight     , case when b.Attribute  ='FinishWeight' then sum(b.value) else 0 end as FinishWeight
from Workordersa a eft join Weights b on a.Ident  =b.OwnerIdent          
group by (case when b.Attribute  ='StartWeight' then sum(b.value) else 0 end)      , (case when b.Attribute  ='FinishWeight' then sum(b.value) else 0 end)

Open in new window

0
 

Author Comment

by:Mystical_Ice
ID: 40378727
And then on top of that, to make sure there are no NULLs (where there isn't a "StartWeight" value for a workorder, only a "FinishWeight"), I'd like the query to use the "FinishWeight" value for both the Start and Finish weight.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40378733
Here is adjusted query for NULLs:

select Workorders, case when b.Attribute  ='StartWeight'  or b.Attribute  is null then sum(b.value) else 0 end as StartWeight     , case when b.Attribute  ='FinishWeight' then sum(b.value) else 0 end as FinishWeight
from Workordersa a eft join Weights b on a.Ident  =b.OwnerIdent          
group by (case when b.Attribute  ='StartWeight' then sum(b.value) else 0 end)      , (case when b.Attribute  ='FinishWeight' then sum(b.value) else 0 end)

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Mystical_Ice
ID: 40378734
Conversion failed when converting the nvarchar value '2268.5' to data type int.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40378740
Added the conversion of NVARCHAR to FLOAT :

select Workorders, case when b.Attribute  ='StartWeight'  or b.Attribute  is null then sum(cast(b.value as float)) else 0 end as StartWeight     , case when b.Attribute  ='FinishWeight' then sum(cast(b.value as float)) else 0 end as FinishWeight
from Workordersa a eft join Weights b on a.Ident  =b.OwnerIdent          
group by (case when b.Attribute  ='StartWeight' then sum(cast(b.value as float)) else 0 end)      , (case when b.Attribute  ='FinishWeight' then sum(cast(b.value as float))else 0 end)

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 40378960
SELECT WO.OwnerIdent,W.StartWeight,W.FinishWeight
  FROM dbo.Workorders WO
  LEFT JOIN (SELECT OwnerIdent,
                    SUM(CASE Attribute WHEN 'StartWeight' THEN Value END) AS StartWeight, 
					SUM(CASE Attribute WHEN 'FinishWeight' THEN Value END) AS FinishWeight
               FROM dbo.Weights 
			   GROUP BY OwnerIdent) W
    ON W.OwnerIdent = WO.OwnerIdent

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 40379078
You can join more than once to the weights table, do it once for the start weight, and again for the finish weight; you can achieve this just by adding conditions to the join, like this:

select
       w.ident
    , isnull( sw.value , 0) AS StartWeight
    , isnull( fw.value , 0) AS FinishWeight
from dbo.Workorders w
left join dbo.Weights sw on w.ident = sw.Ownerident and sw.attribute = 'StartWeight'
left join dbo.Weights fw on w.ident = fw.Ownerident and fw.attribute = 'FinishWeight'

Try also using inner joins, it might not be necessary to use left joins.

The sample data does NOT require left joins; IF you can have a dbo.Workorders.ident which has NO MATCHING RECORDS in dbo.Weights THEN you would need an outer join,
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 150 total points
ID: 40380399
It'll be tricky using direct join/double-joins to output only a single row with both lookup results.

I suggest joining to a subquery to keep the grouping in the subquery only, allowing the main query to use other column(s) from workorders without any hassle or restrictions:


SELECT wo.Ident, --wo.<other_column(s)>,
    ISNULL(we.StartWeight, we.FinishWeight) AS StartWeight,
    we.FinishWeight
FROM dbo.workorders wo
LEFT OUTER JOIN (
    SELECT OwnerIdent,
        MAX(CASE WHEN Attribute = 'StartWeight' THEN Value END) AS StartWeight,
        MAX(CASE WHEN Attribute = 'FinishWeight' THEN Value END) AS FinishWeight
    FROM dbo.weights
    WHERE
        Attribute IN ('StartWeight', 'FinishWeight')        
    GROUP BY OwnerIdent
) AS we ON we.OwnerIdent = wo.Ident
ORDER BY wo.Ident
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40381056
Scott, there is a similar proposal by Sharath at ID: 40378960 using SUM()

There is no indication that MAX() or SUM() is functionally correct.

The implied argument is that you should perform the group by so that a one to one relationship exists from a workorder to the weight data, but IF there wasn't a one-to-one relationship then why take the maximum? or why take the sum? either or both of those options could be dead wrong.

The sample data very clearly displays that each workorder has one corresponding StartWeight  and one corresponding FinishWeight and there is a suggestion that a left join is needed.  That isn't tricky at all.
0
 

Expert Comment

by:Raja Sekhar Reddy
ID: 40381757
This can be done by using PIVOT

CREATE TABLE dbo.Workorders (Ident INT);

INSERT INTO dbo.Workorders values (001),(002),(003),(004),(005)

CREATE TABLE dbo.Weights (OwnerIdent  INT, Attribute VARCHAR(100), Value INT)

INSERT INTO dbo.Weights VALUES (001,'StartWeight',1500), (001,'FinishWeight',1520)
, (002,'StartWeight',1592),(002,'FinishWeight',1692),(003,'StartWeight',1792)
, (003,'FinishWeight',1805),(004,'StartWeight',85),(004 ,'FinishWeight',92)


SELECT *
  FROM (
SELECT wo.Ident, w.[Attribute], w.[Value]
  FROM dbo.Workorders wo left join dbo.weights w
    ON wo.Ident            = w.OwnerIdent
       ) P
PIVOT
(
max (Value)
FOR ATTRIBUTE IN
(StartWeight,  FinishWeight)
) AS pvt
order by Ident

Ident      StartWeight      FinishWeight
1      1500      1520
2      1592      1692
3      1792      1805
4      85      92
5      NULL      NULL
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40381776
I did miss the requirement regarding a null start weight, so use coalesce for that, if start weight is null use finish weigh, and if that is also null then use zero.

select
       w.ident
    , coalesce( sw.value , fw.value ,0) AS StartWeight
    , isnull( fw.value , 0) AS FinishWeight
from dbo.Workorders w
left join dbo.Weights sw on w.ident = sw.Ownerident and sw.attribute = 'StartWeight'
left join dbo.Weights fw on w.ident = fw.Ownerident and fw.attribute = 'FinishWeight'
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40382178
I prefer to segregate such single-table data pulls into their own queries so they don't affect the main query.

If, for example, you try to add totals or other aggregates to the outer function, I think the left joins can make it seem trickier.

You can't have different StartingWeights on a shipment, so that shouldn't be a problem.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 28
SQL SELECT query help 7 34
ms sql + get number in list out of total 7 26
SQL - Update field defined as Text 6 16
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question