Solved

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

Posted on 2014-10-13
12
146 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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:
ScottPletcher 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:ScottPletcher
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now