SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast Mary_Itohan's Avatar
    Join Date
    Dec 2004
    Location
    e-Planet
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    combining two tables into one

    Hello,
    I have two tables are thus and i want to query then to give me the results of date,identifier,fullname, age and total fee, from the two into one

    how do i achieve this ?
    thanks

    MM

    Code:
    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,

    and


    Code:
    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,
    KISS - Keep It Simple Stupid

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mary_Itohan View Post
    ... the results of date,identifier,fullname, age and total fee
    which date, referral or consultation?

    which identifier? you have several "ID" columns, in addition to one specifically named "identifier"

    which fullname? there's one in each table

    which age? there's one in each table

    total fee? calculated how, exactly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •