?
Solved

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

Posted on 2014-10-13
12
Medium Priority
?
150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 600 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 41

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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 600 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 49

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 49

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 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