SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 return results where value exists in another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

David_G
Starting Member

Australia
10 Posts

Posted - 09/14/2013 :  07:13:59  Show Profile  Reply with Quote
Hi, I would like to get results from the two tables below where the ITM$Con_Note value is in both tables.

I would also like to calculate the WeightDif field which is the difference between the two weights.

Also in reality these tables are identical tables in separate identical databases on two PC's using SQL Express 2008 R2
Thanks,

David


ITM$Con_Note ITM$Machine ITM$Date_Time ITM$Weight ITM$Machine ITM$Date_Time ITM$Weight WeightDif
ABC456 ADL01 2013-09-14 20:52:39.087 1.200 ADL02 2013-09-14 20:52:39.090 1.500 0.3
1234567890 ADL01 2013-09-14 20:52:39.087 35.6 ADL02 2013-09-14 20:52:39.090 35.75 0.25


drop table #item1
drop table #item

CREATE TABLE #Item(
	[ITM$Con_Note] [varchar](50) NULL,
	[ITM$Machine] [varchar](10) NULL,
	[ITM$Date_Time] [datetime] NULL,
	[ITM$Weight] [numeric](18, 3) NULL,
	)
Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC123','ADL01',getdate(),10.5)
Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL01',getdate(),1.2)
Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC789','ADL01',getdate(),4.5)
Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL01',getdate(),35.6)


CREATE TABLE #Item1(
	[ITM$Con_Note] [varchar](50) NULL,
	[ITM$Machine] [varchar](10) NULL,
	[ITM$Date_Time] [datetime] NULL,
	[ITM$Weight] [numeric](18, 3) NULL,
	)
Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC1231','ADL02',getdate(),10.75)
Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL02',getdate(),1.5)
Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC7890','ADL02',getdate(),4.55)
Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL02',getdate(),35.75)

select * from #Item AS one
LEFT OUTER JOIN #Item1 AS two
ON 
one.ITM$Con_Note = two.ITM$Con_Note
WHERE one.ITM$Con_Note = two.ITM$Con_Note

Edited by - David_G on 09/14/2013 09:02:04

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/14/2013 :  14:59:09  Show Profile  Reply with Quote
Is this what you want?

select *, (two.ITM$Weight - one.ITM$Weight) AS WeightDif from #Item AS one
INNER JOIN #Item1 AS two
ON 
one.ITM$Con_Note = two.ITM$Con_Note

Edited by - MuMu88 on 09/14/2013 15:14:51
Go to Top of Page

David_G
Starting Member

Australia
10 Posts

Posted - 09/14/2013 :  17:10:00  Show Profile  Reply with Quote
Thank you, The real problem I have is that the real item tables are on two separate databases on separate machines. The databases are called cwc and they both have a table called item.

Is it possible to do the same query?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/14/2013 :  21:38:07  Show Profile  Reply with Quote
Yes, just use four part name like this:
server_name.[database_name].[schema_name].table_name

You can find more information regarding syntax conventions here:
http://technet.microsoft.com/en-us/library/ms177563.aspx
Go to Top of Page

David_G
Starting Member

Australia
10 Posts

Posted - 09/15/2013 :  08:22:18  Show Profile  Reply with Quote
Thanks, I cant seem to get the linked server working.
The other server is loomis-adl2\sqlexpress

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'LOOMIS-IND-ADL2\SQLEXPRESS,
@srvproduct=N'SQL Server' ;
GO

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000