Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


return results where value exists in another table


return results where value exists in another table

Author
Message
david.geoghegan
david.geoghegan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 47
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
LinksUp
LinksUp
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 3982
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
david.geoghegan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 47
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
LinksUp
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 3982
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search