Thanks for you help guys, your suggestion Ajay worked fine, cheers

Dyfed Thomas
Population Health Analyst
DDI: 07 858 5967
Mob: 021 409 800

Midlands Health Network
WEL House
711 Victoria Street
PO Box 983
Hamilton 3240
Phone: 07 839 2888
Fax: 07 834 9242

From: Ajay Askoolum [via R] [mailto:ml-node+s789695n4281558h48@n4.nabble.com]
Sent: Tuesday, 10 January 2012 10:40 p.m.
To: Dyfed Thomas
Subject: Re: Extracting Data from SQL Server

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 <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=0>>
To: dthomas <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=1>>; [hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=2>
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:<[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=3>>        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 <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=4>> 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.
>
>______________________________________________
>[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=5> 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.
______________________________________________
[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=6> 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]]


______________________________________________
[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=7> 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.

________________________________
If you reply to this email, your message will be added to the discussion below:
http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281558.html
To unsubscribe from Extracting Data from SQL Server, click here<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4281000&code=ZHlmZWQudGhvbWFzQG1pZGxhbmRzaG4uaGVhbHRoLm56fDQyODEwMDB8NjI5Mzc3NzM2>.
NAML<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

#####################################################################################
Please Read the following:

This message is for the named person's use only.  It may...{{dropped:23}}

