在Microsoft sql服务器中加入/附加不同的表 [英] Joining/Appending different tables together in Microsoft sql server

查看：119
本文介绍了在Microsoft sql服务器中加入/附加不同的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Hai我在MS SQL SERVER的数据库中有3个不同的表。


  1. LAB_patientreg

  2. patient_master

  3. patient_address

此 LAB_patientreg > patient_master 是包含患者详情的两个表（两个表中的患者是不同的， LAB_patientreg 是关于在实验室中直接注册的患者的表， patient_master 是关于在接待处作为 out 或在 在病人注册到医院的病人），并且 patient_address 是包含注册的病人的地址 patient_master 与 patient_address 连接。我的目标是创建一个视图，其中包含此表中的所有值（即如果 patient_master 有50行， LAB_patientreg 有32行，结果视图应为82行）并且列的数量和类型限制为 LAB_patientreg 的列，但它不工作，是否可以创建一个视图，如我所说的？如果不是，我该怎么做才能获得这样的观点？非常感谢您的帮助。



表格结构如下，



patient_master 

  CREATE TABLE [dbo]。[patient_master]（
 [pid] [int] IDENTITY 1）NOT NULL，
 [date_created] [date] NULL，
 [title] [varchar]（25）NULL，
 [fname] [varchar] b [mname] [varchar]（255）NULL，
 [lname] [varchar]（255）NULL，
 [addr_id] 1）NULL，
 [dob] [date] NULL，
 [dobType] [tinyint] NULL，
 [marital_status] NULL，
 [caste] [int] NULL，
 [aadhar_no] [varchar]（16）NULL，
 [mother_name] [varchar] ] [varchar]（100）NULL，
 [guardian_rltn] [varchar]（100）NULL，
 [allow_sms] [varchar] 10）NULL，
 [occup] [int] NULL，
 [monthly_income] [int] NULL，
 [financial_review_date] [datetime] NULL，
 [language] [int] NULL，
 [referrer] [varchar]（100）NULL，
 [referrer_id] [varchar] ] [varchar]（100）NULL，
 [interpreter_id] [varchar]（100）NULL，
 [deceased_date] ，
 [created_by] [varchar]（100）NULL，
 [changed_by] [varchar]（100）NULL，
 [date_changed] [datetime] [tinyint] NULL，
 [void_reason] [varchar]（255）NULL，$ b $ h [ b [block] [tinyint] NULL，
 [aliasname] [varchar]（50）NULL，
 [confidential] ，PRIMARY KEY（pid）
）;

patient_address 

  CREATE TABLE [dbo]。[patient_address]（
 [pres_house_no] [varchar]（25）NULL，
 [pres_city] [varchar] ，
 [pres_street] [varchar]（50）NULL，
 [perm_destrict] [int] NULL，
 [ [varchar]（25）NULL，
 [perm_district] [int] NULL，
 [perm_district] [varchar] 
 [perm_pincode] [varchar]（10）NULL，
 [home_phone] [varchar]（11）NULL，
 [ other_phone] [varchar]（11）NULL，
 [work_phone] [varchar]（11）NULL，
 [ （50）NULL，
 [emerg_cntct_rltn] [varchar]（50）NULL，
 [emerg_cntct_num] [varchar]（11）NULL，
 [pres_place] [varchar] 
 [perm_place] [varchar]（255）NULL，
 [addr_id] [int] IDENTITY（1,1）NOT NULL，
 [pid] [int] NULL，
 [active] [char]（1）NULL，
 [phone_type] [varchar]（4）NULL，PRIMARY KEY（addr_id）
）;

LAB_patientreg 

  CREATE TABLE [dbo]。[LAB_patientreg]（
 [id] [int] IDENTITY（1,1）NOT NULL，
 [patientid] [varchar] （15）NOT NULL，
 [title] [varchar]（15）NULL，
 [fname] [varchar] ，
 [lname] [varchar]（50）NULL，
 [dob] [smalldatetime] NULL，
 [ [varchar]（15）NULL，
 [id] [varchar]（15）NULL，
 [idno] [varchar] ）NULL，
 [address] [varchar]（300）NULL，
 [phtype] [varchar] $ b [emailid] [varchar]（50）NULL，
 [smsstatus] [int] NULL，
 [mailstatus] [int] NULL，
 [status] b $ b [regcenter] [varchar]（30）NULL，
 [regdate] [smalldatetime] NULL，PRIMARY KEY（patientid）
）;

我试过下面的

 选择patientid，title，fname，mname，lname，NULL作为宗教来自LAB_patientreg WHERE 1 = 1 
联合全部
选择pid，title，fname，mname，lname，religion来自病人
 INNER JOIN patient_address ON patient.pid = patient_address.pid WHERE 1 = 1

执行此操作时，会出现错误，如错误代码209，SQL状态S1000：歧义列名称pid

解决方案

想要联盟在这里选择？



在一个视图中包含表数据，以及能够添加条件到第二个表，它将是这样：

 选择标题，fname，mname，lname，NULL作为宗教，patientid作为pid来自dbo.LAB_patientreg WHERE 1 = 1 
 UNION 
选择标题，fname，mname，lname，religion，convert（varchar，patient_master.pid）作为pid from dbo.patient_master 
 INNER JOIN patient_address ON patient_master.pid = patient_address.pid 
 WHERE 1 = 1

（只需替换其中的1 = 1即可）



使用联盟选择：确保两个表都有相同的列名。如果你没有在其他表中的字段，可以使用 AlternateFieldname As TheFieldNameINeed 例如字段 Null As religion



编辑：您可以使用 Union 获取所有不同的值或 以获取表的所有值。


Hai all I have 3 different tables in a database in MS SQL SERVER.

  1. LAB_patientreg
  2. patient_master
  3. patient_address

This LAB_patientreg and patient_master are two tables containing patient details (the patients in both table are different, LAB_patientreg is the table regarding the patients who registered in lab directly and patient_master is about the patient registered to hospital as out patient or in patient at the reception) and patient_address is the table which contains the address of patients registered in the table patient_master.

In some cases I need the data of all the patients including LAB_patientreg and patient_master with patient_address. My aim is to create a view which have all the values in this table together(ie if patient_master have 50 rows and LAB_patientreg have 32 rows the resultant view should be 82 rows) and also the number and type of columns is limited to that of LAB_patientreg but it is not working, is it possible to create a view as I said? If not what should I do to obtain such view? Thanks in advance for helping me.

Structure of tables are as follows,

patient_master

CREATE TABLE [dbo].[patient_master](
    [pid] [int] IDENTITY(1,1) NOT NULL,
    [date_created] [date] NULL,
    [title] [varchar](25) NULL,
    [fname] [varchar](255) NULL,
    [mname] [varchar](255) NULL,
    [lname] [varchar](255) NULL,
    [addr_id] [int] NULL,
    [sex] [char](1) NULL,
    [dob] [date] NULL,
    [dobType] [tinyint] NULL,
    [marital_status] [int] NULL,
    [religion] [int] NULL,
    [caste] [int] NULL,
    [aadhar_no] [varchar](16) NULL,
    [mother_name] [varchar](100) NULL,
    [guardian_name] [varchar](100) NULL,
    [guardian_rltn] [varchar](100) NULL,
    [allow_sms] [varchar](10) NULL,
    [allow_email] [varchar](10) NULL,
    [occupation] [int] NULL,
    [monthly_income] [int] NULL,
    [financial_review_date] [datetime] NULL,
    [race] [int] NULL,
    [language] [int] NULL,
    [referrer] [varchar](100) NULL,
    [referrer_id] [varchar](100) NULL,
    [interpretter] [varchar](100) NULL,
    [interpretter_id] [varchar](100) NULL,
    [deceased_date] [date] NULL,
    [deceased_reason] [varchar](255) NULL,
    [created_by] [varchar](100) NULL,
    [changed_by] [varchar](100) NULL,
    [date_changed] [datetime] NULL,
    [voided] [tinyint] NULL,
    [voided_by] [varchar](100) NULL,
    [date_voided] [datetime] NULL,
    [void_reason] [varchar](255) NULL,
    [block] [tinyint] NULL,
    [aliasname] [varchar](50) NULL,
    [confidential] [int] NULL,
    [hospno] [char](8) NULL, PRIMARY KEY(pid)
);

patient_address

CREATE TABLE [dbo].[patient_address](
    [pres_house_no] [varchar](25) NULL,
    [pres_city] [varchar](50) NULL,
    [pres_street] [varchar](50) NULL,
    [pres_district] [int] NULL,
    [pres_pincode] [varchar](10) NULL,
    [perm_house_no] [varchar](25) NULL,
    [perm_city] [varchar](50) NULL,
    [perm_street] [varchar](50) NULL,
    [perm_district] [int] NULL,
    [perm_pincode] [varchar](10) NULL,
    [home_phone] [varchar](11) NULL,
    [mobile_phone] [varchar](11) NULL,
    [other_phone] [varchar](11) NULL,
    [work_phone] [varchar](11) NULL,
    [email] [varchar](255) NULL,
    [emerg_cntct] [varchar](50) NULL,
    [emerg_cntct_rltn] [varchar](50) NULL,
    [emerg_cntct_num] [varchar](11) NULL,
    [pres_place] [varchar](255) NULL,
    [perm_place] [varchar](255) NULL,
    [addr_id] [int] IDENTITY(1,1) NOT NULL,
    [pid] [int] NULL,
    [active] [char](1) NULL,
    [phone_type] [varchar](4) NULL, , PRIMARY KEY(addr_id)
);

LAB_patientreg

CREATE TABLE [dbo].[LAB_patientreg](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [patientid] [varchar](15) NOT NULL,
    [title] [varchar](15) NULL,
    [fname] [varchar](50) NULL,
    [mname] [varchar](50) NULL,
    [lname] [varchar](50) NULL,
    [dob] [smalldatetime] NULL,
    [gender] [char](2) NULL,
    [mstatus] [varchar](15) NULL,
    [idtype] [varchar](15) NULL,
    [idno] [varchar](25) NULL,
    [mtongue] [varchar](35) NULL,
    [address] [varchar](300) NULL,
    [phtype] [varchar](15) NULL,
    [phno] [varchar](20) NULL,
    [emailid] [varchar](50) NULL,
    [smsstatus] [int] NULL,
    [mailstatus] [int] NULL,
    [status] [int] NULL,
    [regcenter] [varchar](30) NULL,
    [regdate] [smalldatetime] NULL, PRIMARY KEY(patientid)
);

I had tried the following 

Select patientid,title, fname, mname, lname, NULL As religion from LAB_patientreg WHERE 1=1
Union All  
Select pid,title, fname, mname, lname, religion from patient 
INNER JOIN patient_address ON patient.pid = patient_address.pid WHERE 1=1

When executing this, an error occurs as Error code 209, SQL state S1000: Ambiguous column name 'pid'"

解决方案

you maybe want an Union Select here?

Containing both Table Data in one View, plus being able to add condition to the Second Table. it would be like this:

Select title, fname, mname, lname, NULL As religion,  patientid As pid from dbo.LAB_patientreg WHERE 1=1
UNION  
Select title, fname, mname, lname, religion, convert(varchar, patient_master.pid) As pid from dbo.patient_master 
INNER JOIN patient_address ON patient_master.pid = patient_address.pid
WHERE 1=1

(Just Replace the where 1=1 with your condition)

With the Union Select: Make Sure you have The Same Column Names on both tables. if you dont have the field in the other table, you can use AlternateFieldname As TheFieldNameINeed As for example The field Null As religion

Edit: Either you can use Union to get all different values or Union All to get ALL values of both table.

这篇关于在Microsoft sql服务器中加入/附加不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆