Help with matching contacts to organisations

Question

Help with matching contacts to organisations

harleen5102

SSC-Addicted

Points: 421
More actions
September 23, 2016 at 6:13 am

#400768

I am very new to sql and will appreciate your help on the query I am trying to achieve.
I am unable to upload sql file here so i have uploaded it on google drive with schema and data for Master contacts table and Organisation table. I am trying to match the contacts in the master contacts table to the organisation table.
Sql file: removed
I have written the below query to match the contacts to the organisation based on the Organisation ([Mailing Address Line 1]) and the Post Code [Mailing Zip/Postal Code1]
;with cteMaster as
(
select *
from [CRM].[dbo].[masterContacts]
),
cteOrgs as
(
select *
from [CRM].[dbo].[nhsOrgs]
)
select o.[Organisation Name]
,o.[Organisation ID]
,o.[Office Address Line 1]
,o.[Office Address Line 2]
,O.[Office Address Line 3]
,O.[Office City]
,O.Location
,O.[Office Post Code]
,O.[Organisation Type]
,O.[Organisation Owner]
,M.Salutation
,m.[First Name]
,m.[Last Name]
,m.Title
,o.[Office Address Line 1] as [Mailing Address Line 1]
,o.[Office Address Line 2] as [Mailing Address Line 2]
,O.[Office Address Line 3] as [Mailing Address Line 3]
,m.[Mailing City]
,m.[Mailing State/Province]
,m.[Mailing Zip/Postal Code1]
,m.[Primary owner]
,m.[Secondary owner]
,m.Phone
,m.Mobile
,m.Fax
,m.Email
,m.Team
,m.Specialsim
,m.[Organisation: Phone]
,m.Website
from cteOrgs O
inner join cteMaster M on
o.[Office Post Code] = m.[Mailing Zip/Postal Code1]
AND O.[Organisation Name] = M.[Mailing Address Line 1]
order by O.[Organisation ID]
Above query brings me some matches but not all of them. In the master contacts file there are some records like 'North Middlesex University Hospital' and 'North Middlesex University Hospital NHS Trust' and the above query is missing out some matches.
I will appreciate if anyone can help me optimise the query and show me whats the best solution would be to match the contacts to the organisations.

Viewing 15 posts - 1 through 15 (of 24 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

are you sure you should have posted that information on a public forum?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

harleen5102 SSC-Addicted Points: 421 More actions · Answer 2

J Livingston SQL (9/23/2016)
are you sure you should have posted that information on a public forum?

yes, all information is available to public on data.gov website. Thats where I have got this information from and uploaded into sql tables.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I strongly recommend you remove that file, as you are very likely in violation of the UK data privacy laws by posting personally identifying data in public.

Post made up data for people to write queries against, not real data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

harleen5102 SSC-Addicted Points: 421 More actions · Answer 4

GilaMonster (9/23/2016)
I strongly recommend you remove that file, as you are very likely in violation of the UK data privacy laws by posting personally identifying data in public.
Post made up data for people to write queries against, not real data.

Its all available here to public anyway: https://data.gov.uk/dataset/england-nhs-connecting-for-health-organisation-data-service-data-files-of-nhsorganisations

harleen5102 SSC-Addicted Points: 421 More actions · Answer 5

Data is available to use for anyone by UK government but I have removed the file anyway.

Here are the schemas of two tables and some dummy data

Schema:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[masterContacts](

[Organisation Name] [nvarchar](255) NULL,

[Office Address Line 1] [nvarchar](255) NULL,

[Office Address Line 2] [nvarchar](255) NULL,

[Office Address Line 3] [nvarchar](255) NULL,

[OfficeMailing City] [nvarchar](255) NULL,

[Office Location] [nvarchar](255) NULL,

[check] [nvarchar](255) NULL,

[Office Mailing State/Province] [nvarchar](255) NULL,

[PO lookup] [nvarchar](255) NULL,

[Mailing Zip/Postal Code] [nvarchar](255) NULL,

[Organisation Type] [nvarchar](255) NULL,

[Organisation Owner] [nvarchar](255) NULL,

[Salutation] [nvarchar](255) NULL,

[First Name] [nvarchar](255) NULL,

[Last Name] [nvarchar](255) NULL,

[Title] [nvarchar](255) NULL,

[Report To:] [nvarchar](255) NULL,

[Mailing Address Line 1] [nvarchar](255) NULL,

[Mailing Address Line 2] [nvarchar](255) NULL,

[Mailing Address Line 3] [nvarchar](255) NULL,

[Mailing City] [nvarchar](255) NULL,

[Location] [nvarchar](255) NULL,

[check1] [nvarchar](255) NULL,

[Mailing State/Province] [nvarchar](255) NULL,

[PO lookup1] [nvarchar](255) NULL,

[Primary owner] [nvarchar](255) NULL,

[SO lookup] [nvarchar](255) NULL,

[Secondary owner] [nvarchar](255) NULL,

[Mailing Zip/Postal Code1] [nvarchar](255) NULL,

[Mailing Country] [nvarchar](255) NULL,

[Phone] [nvarchar](255) NULL,

[Mobile] [nvarchar](255) NULL,

[Fax] [nvarchar](255) NULL,

[Email] [nvarchar](255) NULL,

[Team] [nvarchar](255) NULL,

[Specialsim] [nvarchar](255) NULL,

[Organisation: Phone] [nvarchar](255) NULL,

[Organisation: Fax] [nvarchar](255) NULL,

[Website] [nvarchar](255) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[nhsOrgs] Script Date: 23/09/2016 12:56:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[nhsOrgs](

[id] [int] IDENTITY(1,1) NOT NULL,

[Organisation Name] [nvarchar](255) NULL,

[Organisation ID] [nvarchar](255) NULL,

[Parent Organisation] [nvarchar](255) NULL,

[Parent Organisation ID] [nvarchar](255) NULL,

[Office Address Line 1] [nvarchar](255) NULL,

[Office Address Line 2] [nvarchar](255) NULL,

[Office Address Line 3] [nvarchar](255) NULL,

[Office Street] [nvarchar](255) NULL,

[Office City] [nvarchar](255) NULL,

[Office County/Region] [nvarchar](255) NULL,

[Office Post Code] [nvarchar](255) NULL,

[Organisation Type] [nvarchar](255) NULL,

[Organisation Owner] [nvarchar](255) NULL,

[Location] [nvarchar](255) NULL

) ON [PRIMARY]

GO

Dummy Data

INSERT [dbo].[masterContacts] ([Organisation Name], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [OfficeMailing City], [Office Location], [check], [Office Mailing State/Province], [PO lookup], [Mailing Zip/Postal Code], [Organisation Type], [Organisation Owner], [Salutation], [First Name], [Last Name], [Title], [Report To:], [Mailing Address Line 1], [Mailing Address Line 2], [Mailing Address Line 3], [Mailing City], [Location], [check1], [Mailing State/Province], [PO lookup1], [Primary owner], [SO lookup], [Secondary owner], [Mailing Zip/Postal Code1], [Mailing Country], [Phone], [Mobile], [Fax], [Email], [Team], [Specialsim], [Organisation: Phone], [Organisation: Fax], [Website]) VALUES (N'Enfield CCG', N'Barnet, Enfield & Haringey Mental Heath NHS Trust', N'Enfield Community Services', N'B2 St Anns Hospital', N'Tottenham', N'Greater London', N'#REF!', N'London', N'#REF!', N'N13 3TH', N'NHS', N'Peter', N'Mr', N'B', N'Chandler', N'AD Adults & Older People', NULL, N'Flat 3, St Michaels Hospital', N'Gater Drive ', NULL, N'Enfield ', N'Middlesex', N'Middlesex', N'Middlesex', N'BD', N'Sarah Bell', N'AOM', N'Karen Parker', N'EN2 0JB', NULL, N'02083 752859', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [dbo].[masterContacts] ([Organisation Name], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [OfficeMailing City], [Office Location], [check], [Office Mailing State/Province], [PO lookup], [Mailing Zip/Postal Code], [Organisation Type], [Organisation Owner], [Salutation], [First Name], [Last Name], [Title], [Report To:], [Mailing Address Line 1], [Mailing Address Line 2], [Mailing Address Line 3], [Mailing City], [Location], [check1], [Mailing State/Province], [PO lookup1], [Primary owner], [SO lookup], [Secondary owner], [Mailing Zip/Postal Code1], [Mailing Country], [Phone], [Mobile], [Fax], [Email], [Team], [Specialsim], [Organisation: Phone], [Organisation: Fax], [Website]) VALUES (N'Enfield CCG', N'Barnet, Enfield & Haringey Mental Heath NHS Trust', N'Enfield Community Services', N'B2 St Anns Hospital', N'Tottenham', N'Greater London', N'#REF!', N'London', N'#REF!', N'N13 3TH', N'NHS', N'Peter', N'Ms', N'C', N'Challis', N'TB Public Health ', NULL, N'Chest Clinic ', N'North Middlesex University Hospital', N'Sterling Way ', N'London', N'Greater London', N'Greater London', N'London', N'BD', N'Sarah Bell', N'RD', N'Karen Parker', N'N18 1QX', NULL, N'02088 872332', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [dbo].[masterContacts] ([Organisation Name], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [OfficeMailing City], [Office Location], [check], [Office Mailing State/Province], [PO lookup], [Mailing Zip/Postal Code], [Organisation Type], [Organisation Owner], [Salutation], [First Name], [Last Name], [Title], [Report To:], [Mailing Address Line 1], [Mailing Address Line 2], [Mailing Address Line 3], [Mailing City], [Location], [check1], [Mailing State/Province], [PO lookup1], [Primary owner], [SO lookup], [Secondary owner], [Mailing Zip/Postal Code1], [Mailing Country], [Phone], [Mobile], [Fax], [Email], [Team], [Specialsim], [Organisation: Phone], [Organisation: Fax], [Website]) VALUES (N'Epsom and St Helier Unviersity Hospital Trust', N'St Helier Hospital', N'Wrythe Lane', NULL, N'Carshalton', N'Surrey', N'#REF!', N'Surrey', N'#REF!', N'SM5 1AA', N'NHS', N'Peter', N'Mr', N'Daniels', N'Elkeles ', N'Chief Executive', NULL, N'St Helier Hospital', N'Wrythe Lane', NULL, N'Carshalton', N'Surrey', N'Surrey', N'Surrey', N'CEO', N'Peter ', N'BD', N'Sarah Bell', N'SM5 1AA', N'United Kingdom', N'02082 962000', NULL, N'020 8641 4546', N'daniel.elkeles@esth.nhs.uk', NULL, NULL, N'02082 962000', N'020 8641 4546', N'http://www.epsom-sthelier.nhs.uk/')

INSERT [dbo].[masterContacts] ([Organisation Name], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [OfficeMailing City], [Office Location], [check], [Office Mailing State/Province], [PO lookup], [Mailing Zip/Postal Code], [Organisation Type], [Organisation Owner], [Salutation], [First Name], [Last Name], [Title], [Report To:], [Mailing Address Line 1], [Mailing Address Line 2], [Mailing Address Line 3], [Mailing City], [Location], [check1], [Mailing State/Province], [PO lookup1], [Primary owner], [SO lookup], [Secondary owner], [Mailing Zip/Postal Code1], [Mailing Country], [Phone], [Mobile], [Fax], [Email], [Team], [Specialsim], [Organisation: Phone], [Organisation: Fax], [Website]) VALUES (N'Epsom and St Helier Unviersity Hospital Trust', N'St Helier Hospital', N'Wrythe Lane', NULL, N'Carshalton', N'Surrey', N'#REF!', N'Surrey', N'#REF!', N'SM5 1AA', N'NHS', N'Peter', N'Dr', N'Ruth', N'Charlton', N'Deputy CEO/Joint Medical Director ', NULL, N'Epsom General Hospital', N'Dorking Road', NULL, N'Epsom', N'Surrey', N'Surrey', N'Surrey', N'#N/A', N'Karen Parker', N'#N/A', N'Sarah Bell', N'KT18 7EG', N'United Kingdom', N'01372 735735', NULL, N'01372 735310', N'Ruth.charlton@esth.nhs.uk', NULL, NULL, N'01372 735735', N'01372 735310', N'http://www.epsom-sthelier.nhs.uk/')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (175, N'Royal National Orthopaedic Hospital NHS Trust', N'RAN', NULL, NULL, N'Brockley Hill', NULL, NULL, N'Brockley Hill', N'Stanmore', N'Middlesex', N'HA7 4LP', N'NHS', N'Peter', N'Middlesex')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (176, N'The Royal National Orthopaedic Hospital (Stanmore)', N'RAN01', N'Royal National Orthopaedic Hospital NHS Trust', N'RAN', N'Brockley Hill', NULL, NULL, N'Brockley Hill', N'Stanmore', N'Middlesex', N'HA7 4LP', N'NHS', N'Peter', N'Middlesex')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (177, N'Royal National Orthopaedic Hospital (Bolsover Street)', N'RAN02', N'Royal National Orthopaedic Hospital NHS Trust', N'RAN', N'45-51 Bolsover Street', NULL, NULL, N'45-51 Bolsover Street', N'London', N'Greater London', N'W1W 5AQ', N'NHS', N'Peter', N'Greater London')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (178, N'North Middlesex University Hospital NHS Trust', N'RAP', NULL, NULL, N'Sterling Way', NULL, NULL, N'Sterling Way', N'London', N'Greater London', N'N18 1QX', N'NHS', N'Peter', N'Greater London')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (179, N'North Middlesex Hospital', N'RAPNM', N'North Middlesex University Hospital NHS Trust', N'RAP', N'Sterling Way', NULL, NULL, N'Sterling Way', N'London', N'Greater London', N'N18 1QX', N'NHS', N'Peter', N'Greater London')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (180, N'The Hillingdon Hospitals NHS Foundation Trust', N'RAS', NULL, NULL, N'Pield Heath Road', NULL, NULL, N'Pield Heath Road', N'Uxbridge', N'Middlesex', N'UB8 3NN', N'NHS', N'Peter', N'Middlesex')

INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Parent Organisation], [Parent Organisation ID], [Office Address Line 1], [Office Address Line 2], [Office Address Line 3], [Office Street], [Office City], [Office County/Region], [Office Post Code], [Organisation Type], [Organisation Owner], [Location]) VALUES (181, N'Hillingdon Hospital', N'RAS01', N'The Hillingdon Hospitals NHS Foundation Trust', N'RAS', N'Pield Heath Road', NULL, NULL, N'Pield Heath Road', N'Uxbridge', N'Middlesex', N'UB8 3NN', N'NHS', N'Peter', N'Middlesex')

I will appreciate your help regarding this. Thank you

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

ok...thanks for data....didnt realise it was in public domain......nice to know that it is.

looking at your dummy data....dont see that you have any data there that is going to demonstrate your problem .....mayve I am wrong bit would be great if you can double check and amend accordingly.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

harleen5102 SSC-Addicted Points: 421 More actions · Answer 7

J Livingston SQL (9/23/2016)
ok...thanks for data....didnt realise it was in public domain......nice to know that it is.
looking at your dummy data....dont see that you have any data there that is going to demonstrate your problem .....mayve I am wrong bit would be great if you can double check and amend accordingly.

No problem! Any suggestions on my query?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

harleen5102 (9/23/2016)
J Livingston SQL (9/23/2016)
ok...thanks for data....didnt realise it was in public domain......nice to know that it is.
looking at your dummy data....dont see that you have any data there that is going to demonstrate your problem .....mayve I am wrong bit would be great if you can double check and amend accordingly.
No problem! Any suggestions on my query?

provide some dummy data that demonstrates your problem....

ps...please run you dummy sample set up and correct errors and then provide your expected results

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Thom A SSC Guru Points: 99126 More actions · Answer 9

Would you mind pasting a link to where to data is located to on gov.uk (don't provide a link direct to the source, but the page which contains the download link)?

From my experience using data from some public domains, sometimes the problem is the postcode due to it having a space. You end up finding one table has some entries that contain the spaces, while the other does not (or vice versa).

If the data is available on gov.uk it'll be easier to just have a look at the raw data at source and test your query there.

Cheers!

Edit: Nevermind, found it. Which files are you using, wasn't expecting quite so many.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

me thinks that part of your problem is that you are expecting an exact match on O.[Organisation Name] = M.[Mailing Address Line 1]

what happens if you change

inner join cteMaster M on

o.[Office Post Code] = m.[Mailing Zip/Postal Code1]

AND O.[Organisation Name] = M.[Mailing Address Line 1]

to this

inner join cteMaster M on

o.[Office Post Code] = m.[Mailing Zip/Postal Code1]

ps...dont think you need the CTE's either...just join the tables

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

harleen5102 SSC-Addicted Points: 421 More actions · Answer 11

I have provided the schema for tables and some dummy insert data.

The problem is

When i run a SELECT on masterContacts table by using a LIKE wild card I get the following results:

/****** Script for SelectTopNRows command from SSMS ******/

SELECT *

FROM [CRM].[dbo].[masterContacts] m

where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'

I get the following results:

Organisation NameOffice Address Line 1Office Address Line 2Office Address Line 3OfficeMailing CityOffice LocationcheckOffice Mailing State/ProvincePO lookupMailing Zip/Postal CodeOrganisation TypeOrganisation OwnerSalutationFirst NameLast NameTitleReport To:Mailing Address Line 1Mailing Address Line 2Mailing Address Line 3Mailing CityLocationcheck1Mailing State/ProvincePO lookup1Primary ownerSO lookupSecondary ownerMailing Zip/Postal Code1Mailing CountryPhoneMobileFaxEmailTeamSpecialsimOrganisation: PhoneOrganisation: FaxWebsite

North Middlesex University HospitalNorth Middlesex University HospitalSterling WayNULLLondonGreater London#REF!London#REF!N18 1QXNHSPeter BattleMsJulieLoweChief ExecutiveNULLNorth Middlesex University Hospital NHS TrustSterling WayNULLLondonGreater LondonGreater LondonLondonCEOPeter BattleBDSarah BellN18 1QXUnited Kingdom02088 872000NULL020 8887 4219Julie.lowe@nmh.nhs.ukNULLNULL020 8887 2000020 8887 4219http://www.northmid.nhs.uk/

North Middlesex University HospitalNorth Middlesex University HospitalSterling WayNULLLondonGreater London#REF!London#REF!N18 1QXNHSPeter BattleMrPaulReevesDirector of Nursing NULLNorth Middlesex University Hospital NHS TrustSterling WaySterling WayLondonGreater LondonGreater LondonLondonBDSarah BellCOODr. Felix DaviesN18 1QXUnited Kingdom020 8887 2564NULL020 8887 4219paul.reeves@nhs.netNULLNULL020 8887 2000020 8887 4219http://www.northmid.nhs.uk/%5B/code%5D

And the record with same name and post code also exists in the Organisation table:

SELECT *

FROM [CRM].[dbo].[nhsOrgs]

where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'

idOrganisation NameOrganisation IDParent OrganisationParent Organisation IDOffice Address Line 1Office Address Line 2Office Address Line 3Office StreetOffice CityOffice County/RegionOffice Post CodeOrganisation TypeOrganisation OwnerLocation

178North Middlesex University Hospital NHS TrustRAPNULLNULLSterling WayNULLNULLSterling WayLondonGreater LondonN18 1QXNHSPeter BattleGreater London

Now when I run my below query I am unable to get a match on North Middlesex University Hospital NHS Trust even though the Organisation name and post code is same in both tables

;with cteMaster as

(

select *

from [CRM].[dbo].[masterContacts]

),

cteOrgs as

(

select *

from [CRM].[dbo].[nhsOrgs]

)

select o.[Organisation Name]

,o.[Organisation ID]

,o.[Office Address Line 1]

,o.[Office Address Line 2]

,O.[Office Address Line 3]

,O.[Office City]

,O.Location

,O.[Office Post Code]

,O.[Organisation Type]

,O.[Organisation Owner]

,M.Salutation

,m.[First Name]

,m.[Last Name]

,m.Title

,o.[Office Address Line 1] as [Mailing Address Line 1]

,o.[Office Address Line 2] as [Mailing Address Line 2]

,O.[Office Address Line 3] as [Mailing Address Line 3]

,m.[Mailing City]

,m.[Mailing State/Province]

,m.[Mailing Zip/Postal Code1]

,m.[Primary owner]

,m.[Secondary owner]

,m.Phone

,m.Mobile

,m.Fax

,m.Email

,m.Team

,m.Specialsim

,m.[Organisation: Phone]

,m.Website

from cteOrgs O

inner join cteMaster M on

o.[Office Post Code] = m.[Mailing Zip/Postal Code1]

AND O.[Organisation Name] = M.[Mailing Address Line 1]

WHERE M.[Mailing Address Line 1] = 'North Middlesex University Hospital NHS Trust'

order by O.[Organisation ID]

I get 0 results by running the above query. Is there another way of writing the above query so LIKE wildcard can be used to match all the records from mastercontacts and organisation table?

harleen5102 SSC-Addicted Points: 421 More actions · Answer 12

Thom A (9/23/2016)
Would you mind pasting a link to where to data is located to on gov.uk (don't provide a link direct to the source, but the page which contains the download link)?
From my experience using data from some public domains, sometimes the problem is the postcode due to it having a space. You end up finding one table has some entries that contain the spaces, while the other does not (or vice versa).
If the data is available on gov.uk it'll be easier to just have a look at the raw data at source and test your query there.
Cheers!
Edit: Nevermind, found it. Which files are you using, wasn't expecting quite so many.

Hi,

Here is the link: https://data.gov.uk/dataset/england-nhs-connecting-for-health-organisation-data-service-data-files-of-nhsorganisations

But the dummy data I have provided above is the data which I have filtered out from the 27k records provided by the data.gov website and formatted it in a different form. I have already checked the data and there are no spaces.

harleen5102 SSC-Addicted Points: 421 More actions · Answer 13

J Livingston SQL (9/23/2016)
me thinks that part of your problem is that you are expecting an exact match on O.[Organisation Name] = M.[Mailing Address Line 1]
what happens if you change
inner join cteMaster M on
o.[Office Post Code] = m.[Mailing Zip/Postal Code1]
AND O.[Organisation Name] = M.[Mailing Address Line 1]
to this
inner join cteMaster M on
o.[Office Post Code] = m.[Mailing Zip/Postal Code1]
ps...dont think you need the CTE's either...just join the tables

I have tried that but I still can't get 'North Middlesex University Hospital NHS Trust' if i put that in my where clause

from cteOrgs O

inner join cteMaster M on

o.[Office Post Code] = m.[Mailing Zip/Postal Code1]

--AND O.[Organisation Name] = M.[Mailing Address Line 1]

WHERE M.[Mailing Address Line 1] = 'North Middlesex University Hospital NHS Trust'

order by O.[Organisation ID]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

this should be easy to help you...but the sample data isnt doing what you want.

your sample data:

USE tempdb

GO

CREATE TABLE [dbo].[masterContacts](

[Organisation Name] [nvarchar](255) NULL,

[Office Address Line 1] [nvarchar](255) NULL,

[Office Address Line 2] [nvarchar](255) NULL,

[Office Address Line 3] [nvarchar](255) NULL,

[OfficeMailing City] [nvarchar](255) NULL,

[Office Location] [nvarchar](255) NULL,

[check] [nvarchar](255) NULL,

[Office Mailing State/Province] [nvarchar](255) NULL,

[PO lookup] [nvarchar](255) NULL,

[Mailing Zip/Postal Code] [nvarchar](255) NULL,

[Organisation Type] [nvarchar](255) NULL,

[Organisation Owner] [nvarchar](255) NULL,

[Salutation] [nvarchar](255) NULL,

[First Name] [nvarchar](255) NULL,

[Last Name] [nvarchar](255) NULL,

[Title] [nvarchar](255) NULL,

[Report To:] [nvarchar](255) NULL,

[Mailing Address Line 1] [nvarchar](255) NULL,

[Mailing Address Line 2] [nvarchar](255) NULL,

[Mailing Address Line 3] [nvarchar](255) NULL,

[Mailing City] [nvarchar](255) NULL,

[Location] [nvarchar](255) NULL,

[check1] [nvarchar](255) NULL,

[Mailing State/Province] [nvarchar](255) NULL,

[PO lookup1] [nvarchar](255) NULL,

[Primary owner] [nvarchar](255) NULL,

[SO lookup] [nvarchar](255) NULL,

[Secondary owner] [nvarchar](255) NULL,

[Mailing Zip/Postal Code1] [nvarchar](255) NULL,

[Mailing Country] [nvarchar](255) NULL,

[Phone] [nvarchar](255) NULL,

[Mobile] [nvarchar](255) NULL,

[Fax] [nvarchar](255) NULL,

[Email] [nvarchar](255) NULL,

[Team] [nvarchar](255) NULL,

[Specialsim] [nvarchar](255) NULL,

[Organisation: Phone] [nvarchar](255) NULL,

[Organisation: Fax] [nvarchar](255) NULL,

[Website] [nvarchar](255) NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[nhsOrgs] Script Date: 23/09/2016 12:56:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[nhsOrgs](

[id] [int] ,