联接四个表sqlserver 2005 [英] joining four tables sqlserver 2005

问题描述

我正在使用三个表

I am using three table

Patient(
    [LASID] [int] IDENTITY(10000000,1)  NOT NULL,IS my primary key
    [PatientName] [varchar](30) NULL,
    [Age] [int] NOT NULL,
    [AgeUnit] [char](1) NOT NULL,
    [Sex] [char](1) NOT NULL,
    [DOB] [datetime] NOT NULL,
    [Relation] [varchar](4) NOT NULL,
    [RelativeName] [varchar](30) NULL,
    [Priorty] [char](1) NOT NULL,
    [DiscountBy] [int] NULL,
 )
Test(
    [TestID] [int] IDENTITY(1,1) NOT NULL, is primary key
    [TestName] [varchar](30) NOT NULL,
    [Acronym] [varchar](15) NOT NULL,
    [Charges] [real] NOT NULL,
    [Urgent] [char](1) NULL,
    [TestType] [char](1) NOT NULL,
    [ResultType] [char](1) NULL,
    [Ranges] [char](1) NOT NULL,
    [Active] [char](1) NULL,
    [Dorder] [int] NULL,
 )
Ranges(
    [RangeID] [int] IDENTITY(1,1) NOT NULL, is Primary key
    [TestID] [int] NOT NULL,
    [Sex] [char](1) NOT NULL,
    [AgeType] [char](1) NULL,
    [MinAge] [int] NOT NULL,
    [MaxAge] [int] NULL,
    [MinRange] [float] NULL,
    [MaxRange] [float] NULL,
    [PrintableRange] [varchar](50) NULL,
 )
PatienTest(
    [LASID] [int] NOT NULL,
    [TestID] [int] NOT NULL,
    [DelDate] [datetime] NOT NULL,
    [TestStatus] [char](1) NOT NULL,
    [ReadytoPrint] [char](1) NULL,
    [Urgent] [char](1) NOT NULL,
    [Charges] [real] NULL,
    [WorkListNo] [int] NULL,
)

病人与测试台有一对多的关系
和测试表与范围表有一对多的关系，
测试表与PatienTest表有一对多的关系
现在我要

Patient has One to many relation to Test table
and Test table has one to many relation to Ranges table,
Test table has one to many relation to PatienTest table
now I want to

 select(Patient.LASID,Patient.PatientName,Patient.Age,Patient.Sex,Patient.RelativeName,Test.TestID,Test.TestName,Test.TestType,PatienTest.TestStatus,PatienTest.DelDate,PatienTest.ReadytoPrint,Ranges.Sex,Ranges.MinAge,Ranges.MaxAge,Ranges.MinRange,Ranges.MaxRange,Ranges.PrintableRange)
Where(Patient.LASID=124587)and(Patient.Age<=Ranges.MinAge and Patient.Age>=Ranges.MaxAge)And(Patient.Sex=Ranges.Sex)

如何连接这些仅选择一行的表
请任何人帮我.

how can I join these tables that select only one rows
Please any one can help me

推荐答案

正确的方法在很大程度上取决于您的数据，因为您没有对数据进行更多描述，这将是一个猜测.但是，例如，通过以下方式添加FROM，可以让您入门.如上所述，连接表可能不是正确的方法，但可以测试输出:

The correct way depends highly on your data since you didn''t describe the data more, it''ll be a guess. But adding the FROM for example the following way gets you started. As said might not be the correct way to join the tables but lets you to test the output:

FROM Patient p
     INNER JOIN PatientTest pt ON p.PatientID = pt.PAtientID
     INNER JOIN Test t ON t.TestID = pt.TestID
     INNER JOIN Ranges r ON r.TestID = t.TestID

这篇关于联接四个表sqlserver 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！