return results where value exists in another table

Question

Post reply

return results where value exists in another table

david.geoghegan

SSC Enthusiast

Points: 106
More actions
September 14, 2013 at 5:54 am

#298774

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
Topic Next Topic

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

You have a query that returns 99.9% of what you want. All that is needed is to subtract the 2 weights.

select *, two.ITM$Weight - one.ITM$Weight as WeightDiff

from etc . . .

NOTE: Your expected output contained an error in the WeightDiff in the 2nd row.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

david.geoghegan SSC Enthusiast Points: 106 More actions · Answer 2

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?

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

This link might be helpful.

http://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers]

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/