Join Statement - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Join Statement

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-22 : 23:02:02

Guys,

Kindly please help me on how to get the derive result based on join statement. could not get the exact result.

please see below sample data and result: thanks.




CREATE TABLE #SAMPLE1
(itemid nvarchar(35),  inventtransid nvarchar(35), inventdimid nvarchar(35), JOURNALID nvarchar(35))
INSERT INTO #SAMPLE1
SELECT 'MOT2979','LT280264589','DIM4508728','PJ01716356' union all
SELECT 'MOT2979','LT280264590','DIM7043874','PJ01716356'



CREATE TABLE #SAMPLE2
(itemid nvarchar(35),  inventtransid nvarchar(35), toinventtransid nvarchar(35),  inventdimid nvarchar(35), toinventdimid nvarchar(35), JOURNALID nvarchar(35))
INSERT INTO #SAMPLE2
SELECT 'MOT2979','LT280264589','LT280264590','DIM4508728','DIM7043874','PJ01716356' 


CREATE TABLE #SAMPLE3
(inventdimid nvarchar(35), loation nvarchar(35))
INSERT INTO #SAMPLE3
SELECT 'DIM7043874','MARKET-L6' union all
SELECT 'DIM4508728','PR-L4-13'



RESULT:
itemid----FROMLOCATION--TOLOCATION
MOT2979--  MARKET-L6----PR-L4-13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:41:58

[code]
SELECT s2.itemid,s31.loation AS FROMLOCATION,s3.loation AS TOLOCATION
FROM #SAMPLE2 s2
INNER JOIN#SAMPLE3 s3
ON s3.inventdimid = s2.inventdimid
INNER JOIN#SAMPLE3 s31
ON s31.inventdimid = s2.toinventdimid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-23 : 01:54:39

Thanks Visakh for your prompt reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 02:04:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources