return results where value exists in 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)

return results where value exists in another table

Author

Topic

David_G
Starting Member

10 Posts

Posted - 2013-09-14 : 07:13:59

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-14 : 14:59:09

Is this what you want?
[CODE]
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
[/CODE]

David_G
Starting Member

10 Posts

Posted - 2013-09-14 : 17:10:00

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?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-14 : 21:38:07

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

David_G
Starting Member

10 Posts

Posted - 2013-09-15 : 08:22:18

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources