try:

SELECT a.UNIQUE_ID, 
       a.diag01 
  from LoadPUS a
left join CVD_ICD10 b
on a.diag01 = b.[ICD-10 Codes] 
   or a.diag02 = b.[ICD-10 Codes] 
   or a.diag03 = b.[ICD-10 Codes]

I am not sure why your table name CVD_ICD10 has a suffix $.



________________________________
 From: Jeff Newmiller <jdnewmil@dcn.davis.ca.us>
To: dthomas <dyfed.thomas@midlandshn.health.nz>; r-help@r-project.org 
Sent: Tuesday, 10 January 2012, 8:00
Subject: Re: [R] Extracting Data from SQL Server

This is OT here. However, you might want to investigate the UNIQUE keyword in the SQL Server documentation for SELECT.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil@dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.

dthomas <dyfed.thomas@midlandshn.health.nz> wrote:

>Hi, 
>
>I am new to R (and rusty on SQL!) and I'm trying to extract records
>from a
>SQL server database. I have a table of patient records (LoadPUS) which
>have
>three code columns which i want to evaluate against a list of
>particular
>codes (CVD_ICD$ table). Given the size of the patient table I want to
>restrict the data I pull into R to the data I only want to analyse so I
>am
>using SQL to do this. The code i have is as follows:
>
>library(RODBC)
>channel<-odbcConnect("NatCollections")
>query<-"SELECT UNIQUE_ID, diag01 from LoadPUS 
>WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN
>(SELECT [ICD-10 Codes] From CVD_ICD10$))
>OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$))"
>
>This returns duplicate values, I don't want to hardcode the values
>because
>it is quite a long list. Running the "IN" function just for "diag01"
>returns
>the correct number of records, however when combining with another "IN"
>function it doesn't return the correct number of records. Can you see
>where
>my SQL is incorrect or is there another way of doing this?
>
>Much appreciated,
>D
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help@r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.

______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
	[[alternative HTML version deleted]]

