Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

return results where value exists in another table Expand / Collapse
Author
Message
Posted Saturday, September 14, 2013 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 2:04 PM
Points: 4, Visits: 24
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
Post #1494800
Posted Saturday, September 14, 2013 8:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 346, Visits: 1,392
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/
Post #1494814
Posted Saturday, September 14, 2013 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 2:04 PM
Points: 4, Visits: 24
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?
Post #1494843
Posted Saturday, September 14, 2013 11:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 346, Visits: 1,392
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/
Post #1494859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse