CREATE TABLE [dbo].[clt_referral](
	[refID] [int] IDENTITY(1000000,1) NOT NULL,
	[refDate] [datetime] NOT NULL CONSTRAINT [DF_ctl_referral_refDate]  DEFAULT (getdate()),
	[notes] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[GP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PharmacyID] [int] NULL,
	[exclusionIDs] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PatientID] [int] NOT NULL CONSTRAINT [DF_clt_referral_PatientID]  DEFAULT (1),
	[Age] [int] NOT NULL CONSTRAINT [DF_clt_referral_Age]  DEFAULT (0),
	[consultation_date_time] [datetime] NOT NULL CONSTRAINT [DF_clt_referral_consultation_date_time]  DEFAULT (getdate()),
	[faxed] [bit] NOT NULL CONSTRAINT [DF_clt_referral_faxed]  DEFAULT (0),
	[service_fee] [numeric](18, 2) NOT NULL CONSTRAINT [DF_clt_referral_service_fee]  DEFAULT (0),
	[vat] [numeric](18, 2) NOT NULL CONSTRAINT [DF_clt_referral_vat]  DEFAULT (0),
	[fullname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[identifier] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CREATE TABLE [dbo].[clt_record](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fullname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[identifier] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[patientID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[id_old] [int] NULL CONSTRAINT [DF__clt_recor__id_ol__014935CB]  DEFAULT (0),
	[age] [int] NULL,
	[post_code] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ethnicity_id] [int] NULL,
	[consultation_date_time] [datetime] NOT NULL,
	[assessment_1] [bit] NOT NULL,
	[assessment_2] [bit] NOT NULL,
	[assessment_3] [bit] NOT NULL,
	[assessment_4] [bit] NOT NULL,
	[assessment_5] [bit] NULL,
	[current_medication] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[current_illness] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[clt_contra_indication] [bit] NOT NULL,
	[counselling_1] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_1]  DEFAULT (0),
	[counselling_2] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_2]  DEFAULT (0),
	[counselling_3] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_3]  DEFAULT (0),
	[counselling_4] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_4]  DEFAULT (0),
	[counselling_5] [bit] NULL CONSTRAINT [DF_clt_record_counselling_5]  DEFAULT (0),
	[counselling_6] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_6]  DEFAULT (0),
	[counselling_7] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_7]  DEFAULT (0),
	[counselling_8] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_8]  DEFAULT (0),
	[counselling_9] [bit] NOT NULL CONSTRAINT [DF_clt_record_counselling_9]  DEFAULT (0),
	[counselling_10] [bit] NULL,
	[supply_given] [bit] NOT NULL,
	[batch_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[batch_expiry] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[exclusion_ids] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[referral_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[condoms_provided] [bit] NOT NULL,
	[Chlamydia_supplied] [bit] NOT NULL,
	[consultation_time] [int] NOT NULL,
	[service_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_service_fee]  DEFAULT (0),
	[products_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_products_fee]  DEFAULT (0),
	[products_fee_vat] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_products_fee1]  DEFAULT (0),
	[total_fee] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_total_fee]  DEFAULT (0),
	[total_fee_vat] [numeric](6, 2) NOT NULL CONSTRAINT [DF_clt_record_total_fee1]  DEFAULT (0),
	[pharmacy_id] [int] NOT NULL,
	[user_created] [int] NOT NULL,
	[date_created] [datetime] NOT NULL CONSTRAINT [DF_clt_record_date_created]  DEFAULT (getdate()),
	[state] [int] NOT NULL CONSTRAINT [DF_clt_record_state]  DEFAULT (0),
	[inclusion_ids] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[gender] [bit] NULL,
	[patient_id] [int] NULL,
	[Referral] [bit] NULL,

SELECT *
FROM clt_record rd
INNER JOIN clt_referral rf
	ON rd.referral_id = rf.RefID