Get a column from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get a column from another table

Author

Topic

vinwarwick
Starting Member

3 Posts

Posted - 2011-11-24 : 09:56:44

Hi this could be an easy question but having trouble doing this.

I have two tables on of which I want ONLY the max(date) from the second table if this exists.. If not I still want to see the first tables information. I have tried numerous things but if the value in the second table has nothing I get nothing back

Example Tables
Table One:
PartNumber, QtyOnHand, QtyAllocated

Table Two:
PartNumber, DateLastUsed, TrnType

I only want the max(DateLastUsed) if trnType = 'I'

So the result would look like

PartNumber, QtyOnHand, QtyAllocated, LastUsed
12234 10 16 01/01/2011
56767 0 00 NULL
678687 20 30 22/12/2011

Hope this all makes sense HELP!

Vin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-24 : 10:43:50

You should be able to use the max function with a left join like this:


select
   t1.PartNumber,
   t1.QtyOnHand,
   t1.QtyAllocated,
   max(t2.DateLastUsed) as LastUsed
from
   Table1 t1
   left join Table2 t2 on t1.PartNumber = t2.PartNumber
group by
   t1.PartNumber,
   t1.QtyOnHand,
   t1.QtyAllocated

timjohnstone
Starting Member

1 Post

Posted - 2011-11-24 : 10:55:04

A Left Outer Join and a Case statement would do this. Making sure you get all the rows you want, when using an outer join, can get very complex!

It's used as follows:

SELECT PartNumber, QtyOnHand, QtyAllocation, Case When Coalesce(T2.trnType, '') = 'I' Then Max(DateLastUsed) Else NULL End AS LastUsed
FROM [Table1] AS T1
LEFT OUTER JOIN [Table2] AS T2 ON T1.PartNumber = T2.PartNumber

There are other options, such as using a sub-select query:
SELECT PartNumber, QtyOnHand, QtyAllocation, (Select Max(DateLastUsed) From [Table2] AS T2 WHERE T2.PartNumber = T1.PartNumber AND T2.TrnType = 'I') AS LastUsed
FROM [Table1] AS T1

vinwarwick
Starting Member

3 Posts

Posted - 2011-11-24 : 11:03:03

Hi Tim, Thanks for help..

This works fine until I put in the where TrnType = 'I' I only get one value back when there should be 48 values... If TryType is not there e I want to leave it blank but show the other records as well.

SELECT InvWarehouse.StockCode, InvWarehouse.Warehouse, QtyOnHand, QtyInInspection, QtyOnOrder, QtyAllocatedWip, QtyAllocated, YtdQtyIssued, YtdQtySold, YtdUsageValue, InvWarehouse.UnitCost,
DateLastPurchase, DateLastSale, YtdSalesValue, MAX(InvMovements.EntryDate) as MxDate
FROM InvWarehouse
Left Join InvMovements on InvWarehouse.StockCode = InvMovements.StockCode
Where (InvWarehouse.QtyOnHand > 0 or InvWarehouse.QtyInInspection > 0) and InvWarehouse.Warehouse = 'KS' and InvMovements.TrnType = 'I'

Group by InvWarehouse.StockCode, InvWarehouse.Warehouse, QtyOnHand,
QtyInInspection, QtyOnOrder, QtyAllocatedWip, QtyAllocated,
YtdQtyIssued, YtdQtySold, YtdUsageValue, InvWarehouse.UnitCost,
DateLastPurchase, DateLastSale, YtdSalesValue

Thanks again

Vin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-24 : 12:06:45

Remove the TrnType from the where clause and put that in the join condition as in


FROM
	InvWarehouse
	LEFT JOIN InvMovements
		ON  InvWarehouse.StockCode = InvMovements.StockCode 
                    AND InvMovements.TrnType = 'I'

vinwarwick
Starting Member

3 Posts

Posted - 2011-11-24 : 12:12:46

quote:
Originally posted by sunitabeck

Remove the TrnType from the where clause and put that in the join condition as in

FROM
InvWarehouse
LEFT JOIN InvMovements
ON InvWarehouse.StockCode = InvMovements.StockCode
AND InvMovements.TrnType = 'I'

Excellent you are a STAR THANK YOU!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-11-24 : 12:16:47

As soon as you include any column from the outer (non-preserved) table, you essentially convert the outer join to an inner join. You can move that criteria into the join or, we can change the query a bit. I like this approach because (for me) it is a bit easier to read:


   With invMovements (StockCode, EntryDate)
     As ( 
 Select t.StockCode
        ,max(t.EntryDate)
   From dbo.InvMovements        t
  Where t.TrnType = 'I'
  Group By
        t.StockCode
        )
 Select i.StockCode
        ,i.Warehouse
        ,i.QtyOnHand
        ,i.QtyInInspection
        ,i.QtyOnOrder
        ,i.QtyAllocatedWip
        ,i.QtyAllocated
        ,i.YtdQtyIssued
        ,i.YtdQtySold
        ,i.YtdUsageValue
        ,i.UnitCost
        ,i.DateLastPurchase
        ,i.DateLastSale
        ,i.YtdSalesValue
        ,m.EntryDate As MaxEntryDate
   From dbo.InvWarehouse        i
   Left Join invMovements       m On m.StockCode = i.StockCode
  Where i.Warehouse = 'KS'
    And (
        i.QtyOnHand > 0 
     Or i.QtyInInspection > 0
        );

What this does is move the calculation of the max date to a CTE, then we can outer join to the CTE to get the max date. Simplifies the grouping also.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources