SQL View - Linking Two Tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL View - Linking Two Tables

Author

Topic

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 10:48:49

Hi Group,
I am new to SQL Programming and trying to write a select statement to create the SQL View.
I have a two tables:
Table 1 :
Part Number - Unique
Inventory Qty

Table 2 :
PO Number
Line Number ( PO Number + Line Number - Unique )
Part Number
Price
PO Date

I need to do a select statement on Table 1 unique by part number and show the Latest price by po date for the linked part number from table 2.

Thanks
Lado

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 11:22:33

Perhaps this could get you started:


create view myView
as

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,t2.Price as LatestPrice
       ,t2.[PO Date] as LatestPODate
from   table1 t1
cross apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

go

Be One with the Optimizer
TG

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 11:58:46

quote:

Originally posted by TG

Perhaps this could get you started:


create view myView
as

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,t2.Price as LatestPrice
       ,t2.[PO Date] as LatestPODate
from   table1 t1
cross apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

go

Be One with the Optimizer
TG

Hi TG,

Thanks for the quick reply.

Anyway, I have tested this and in fact it does works but trimming records from the original select statement. I want to show all records from the table 1 and show latest price from table 2 if link found other wise print price as 0.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 12:07:20

I see. Then change the CROSS APPLY to OUTER APPLY and change the t2 columns to COALESCE:

,COALESCE(t2.Price, 0) as LatestPrice

Be One with the Optimizer
TG

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 13:03:34

quote:
Originally posted by TG

I see. Then change the CROSS APPLY to OUTER APPLY and change the t2 columns to COALESCE:

,COALESCE(t2.Price, 0) as LatestPrice

Be One with the Optimizer
TG

Hi TG,
Ok, it works.
Can I use LatestPrice ( which is a combination of expression in to another sql column expression like LatestPrice*Qty As TotalValue ?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 13:14:47

sure - like this:


select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,coalesce(t2.Price, 0) as LatestPrice
       ,coalesce(t2.Price, 0) * t1.[Inventory Qty] as TotalValue
       ,t2.[PO Date] as LatestPODate
from   table1 t1
outer apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

Be One with the Optimizer
TG

ladowali
Starting Member

16 Posts

Posted - 2013-11-06 : 13:44:19

quote:

Originally posted by TG

sure - like this:


select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,coalesce(t2.Price, 0) as LatestPrice
       ,coalesce(t2.Price, 0) * t1.[Inventory Qty] as TotalValue
       ,t2.[PO Date] as LatestPODate
from   table1 t1
outer apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

Be One with the Optimizer
TG

Thanks a lot TG..

Lado

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources