September 23, 2016 at 9:50 am
I might be missing something here, but masterContacts.[Mailing Address Line 1] contains no records that have the value N'North Middlesex University Hospital NHS Trust'. Your where clause is filtering on that, so you won't return any results.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 23, 2016 at 9:58 am
J Livingston SQL (9/23/2016)
this should be easy to help you...but the sample data isnt doing what you want.your sample data:
USE tempdbGO
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] ,
[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
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')
SELECT *
FROM [masterContacts] m
where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
FROM [nhsOrgs]
where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
DROP TABLE [masterContacts]
DROP TABLE [nhsOrgs]
Sorry, I must have missed the dummy record. Please try this:
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] ,
[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
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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Ms', N'Julie', N'Lowe', N'Chief Executive', NULL, N'North Middlesex University Hospital NHS Trust', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'CEO', N'Peter Battle', N'BD', N'Sarah Bell', N'N18 1QX', N'United Kingdom', N'02088 872000', NULL, N'020 8887 4219', N'Julie.lowe@nmh.nhs.uk', NULL, NULL, N'020 8887 2000', N'020 8887 4219', N'http://www.northmid.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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Mr', N'B', N'Nugent ', N'Mental Health Liaison', NULL, N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'AOM', N'Karen Parker', N'BD', N'Sarah Bell', N'N18 1QX', NULL, N'020 8887 2000', NULL, NULL, NULL, NULL, N'MH', 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'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')
SELECT *
FROM [masterContacts] m
where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
FROM [nhsOrgs]
where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
DROP TABLE [masterContacts]
DROP TABLE [nhsOrgs]
September 23, 2016 at 9:59 am
Thom A (9/23/2016)
I might be missing something here, but masterContacts.[Mailing Address Line 1] contains no records that have the value N'North Middlesex University Hospital NHS Trust'. Your where clause is filtering on that, so you won't return any results.
Yes, it does. Please try the following
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] ,
[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
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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Ms', N'Julie', N'Lowe', N'Chief Executive', NULL, N'North Middlesex University Hospital NHS Trust', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'CEO', N'Peter Battle', N'BD', N'Sarah Bell', N'N18 1QX', N'United Kingdom', N'02088 872000', NULL, N'020 8887 4219', N'Julie.lowe@nmh.nhs.uk', NULL, NULL, N'020 8887 2000', N'020 8887 4219', N'http://www.northmid.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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Mr', N'B', N'Nugent ', N'Mental Health Liaison', NULL, N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'AOM', N'Karen Parker', N'BD', N'Sarah Bell', N'N18 1QX', NULL, N'020 8887 2000', NULL, NULL, NULL, NULL, N'MH', 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'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')
SELECT *
FROM [masterContacts] m
where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
FROM [nhsOrgs]
where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
DROP TABLE [masterContacts]
DROP TABLE [nhsOrgs]
September 23, 2016 at 10:45 am
seems we are a bit confused between ourselves
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] ,
[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
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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Ms', N'Julie', N'Lowe', N'Chief Executive', NULL, N'North Middlesex University Hospital NHS Trust', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'CEO', N'Peter Battle', N'BD', N'Sarah Bell', N'N18 1QX', N'United Kingdom', N'02088 872000', NULL, N'020 8887 4219', N'Julie.lowe@nmh.nhs.uk', NULL, NULL, N'020 8887 2000', N'020 8887 4219', N'http://www.northmid.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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Mr', N'B', N'Nugent ', N'Mental Health Liaison', NULL, N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'AOM', N'Karen Parker', N'BD', N'Sarah Bell', N'N18 1QX', NULL, N'020 8887 2000', NULL, NULL, NULL, NULL, N'MH', 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'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')
SELECT *
FROM [masterContacts] m
where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
FROM [nhsOrgs]
where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
SELECT M.*, O.*
FROM masterContacts AS M INNER JOIN
nhsOrgs AS O ON M.[Mailing Zip/Postal Code] = O.[Office Post Code]
SELECT M.*, O.*
FROM masterContacts AS M INNER JOIN
nhsOrgs AS O ON M.[Mailing Zip/Postal Code] = O.[Office Post Code]
AND O.[Organisation Name] = M.[Mailing Address Line 1]
DROP TABLE [masterContacts]
DROP TABLE [nhsOrgs]
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2016 at 11:15 am
J Livingston SQL (9/23/2016)
seems we are a bit confused between ourselves
USE tempdbGO
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] ,
[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
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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Ms', N'Julie', N'Lowe', N'Chief Executive', NULL, N'North Middlesex University Hospital NHS Trust', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'CEO', N'Peter Battle', N'BD', N'Sarah Bell', N'N18 1QX', N'United Kingdom', N'02088 872000', NULL, N'020 8887 4219', N'Julie.lowe@nmh.nhs.uk', NULL, NULL, N'020 8887 2000', N'020 8887 4219', N'http://www.northmid.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'North Middlesex University Hospital', N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'#REF!', N'London', N'#REF!', N'N18 1QX', N'NHS', N'Peter Battle', N'Mr', N'B', N'Nugent ', N'Mental Health Liaison', NULL, N'North Middlesex University Hospital', N'Sterling Way', NULL, N'London', N'Greater London', N'Greater London', N'London', N'AOM', N'Karen Parker', N'BD', N'Sarah Bell', N'N18 1QX', NULL, N'020 8887 2000', NULL, NULL, NULL, NULL, N'MH', 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'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')
SELECT *
FROM [masterContacts] m
where M.[Mailing Address Line 1] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
FROM [nhsOrgs]
where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
SELECT M.*, O.*
FROM masterContacts AS M INNER JOIN
nhsOrgs AS O ON M.[Mailing Zip/Postal Code] = O.[Office Post Code]
SELECT M.*, O.*
FROM masterContacts AS M INNER JOIN
nhsOrgs AS O ON M.[Mailing Zip/Postal Code] = O.[Office Post Code]
AND O.[Organisation Name] = M.[Mailing Address Line 1]
DROP TABLE [masterContacts]
DROP TABLE [nhsOrgs]
Dont understand why it is showing 0 results on your last query based on the organisation name as its quite important to match on both post code and org name
September 23, 2016 at 11:17 am
deleted
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2016 at 11:20 am
Results based on exact match on the post code and org name.
September 23, 2016 at 11:26 am
'North Middlesex University Hospital' is not an exact match for 'North Middlesex University Hospital NHS Trust'
does this make sense?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2016 at 11:49 am
I do understand that. Both tables have 'North Middlesex University Hospital NHS Trust' and 'N18 1QX' but when you join the tables and match on name and postcode it fails to show any result?
September 23, 2016 at 12:01 pm
There are leading tabs in some of the data.
For example, the [Mailing Address Line 1] that seems to be 'North Middlesex University Hospital NHS Trust' is actually that same string with a leading horizontal tab. The [Organisation Name] in the corresponding row in the other table does not have the leading tab, so they do not match.
You'll want to clean up all that before you can do any straightforward joining.
Cheers!
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply