One to many join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

One to many join

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 11:20:17

I have an application that produces reports using SQL as an underlying datasource. This SQL is executed on a SQL Server database. The application is bogged down with too many users running these reports. So I have been tasked with recreating these reports directly from Reporting Services.

I am planning to use the SQL from the application in stored procedures and create the reports from that so we can bypass the application and go directly to the database.

My question is simple, I think. In this reporting application, the queries are listed and I can just copy and paste. But sometimes there is 2 or more snippets of SQL tied together on some sort of relationship diagram that the application creates. I can click on one or the other (evidently derived tables) and see the SQL for each. There is a line connecting the "tables" and there is a 1 or many symbol on the "table" at the end of each line. The one I am working on is one-to-many. Evidently, I should be joining these snippets together. I have done so and it runs find in SSMS, but I don't know whether to use an INNER JOIN, FULL OUTER JOIN, LEFT JOIN, etc. That is my question. When I try different joins, I get the same result - this time. But I want to make sure I get it right every time. Is a one-to-many JOIN a LEFT JOIN? And what would a ONE to ONE or MANY to MANY or MANY to ONE join be. I have looked around for a clear explanation, but can't come up with a good one. Thanks for the help.

Duane

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 11:43:36

do you have access the the SQL of the original reports without using the old application?

If they are stored procs in the database you may jsut be able to script them out. You'll then be able to see whether they are LEFT OUTER's or something else.

I don't have the clearest picture of the situation you describe so if this is irrelevant I apologise.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 12:13:30

This is the SQL - it is in the application and is evidently passed through to SQL Server:

--SQL for plPt_Basic - This is the main query. You will see the other one below.
--This SQL is created on the fly based first of all on a type of graphical interface
--by the report designer. The parameters are created on the fly and are described at the
--bottom of this post. But they don't really matter. I just want to use the proper join.
SELECT DISTINCT PT_BASIC.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FULL,
PT_ADMISSION.ADMIT_DATE,
PT_ADMISSION.TERMINATION_DATE,
C_DIAGNOSIS.DIAGNOSIS,
C_DIAGNOSIS.ICD9_CODE,
O_DATASET.DATASET_NAME,
PT_PAY_SOURCE.INSURANCE_CODE,
PT_PAY_SOURCE.INSURANCE_COMPANY,
O_TEAM.TEAM_DESCRIPTION,
VW_RESOURCES_2.ORGANIZATION_NAME,
Case when PT_PAY_SOURCE.INSURANCE_COMPANY is null then A_INSURANCE_TYPE.DESCRIPTION else A_INSURANCE_COMPANY.COMPANY_NAME end Case_when_PT_PAY_SOURCE_I,
Case when VW_RESOURCES.ORGANIZATION_NAME is null then VW_RESOURCES.NAME_LAST + ', ' + VW_RESOURCES.NAME_FIRST else VW_RESOURCES.ORGANIZATION_NAME end Case_when_VW_RESOURCES_OR
FROM PT_BASIC PT_BASIC
LEFT OUTER JOIN PT_ADMISSION PT_ADMISSION ON
(PT_ADMISSION.PATIENT_ID = PT_BASIC.PATIENT_ID)
LEFT OUTER JOIN PT_STATUS PT_STATUS ON
(PT_STATUS.ADMISSION_ID = PT_ADMISSION.ADMISSION_ID)
AND (PT_STATUS.PATIENT_ID = PT_ADMISSION.PATIENT_ID)
AND (PT_STATUS.STATUS_DATE = PT_ADMISSION.ADMIT_DATE)
LEFT OUTER JOIN PTC_DIAGNOSIS PTC_DIAGNOSIS ON
(PTC_DIAGNOSIS.PT_DIAGNOSIS_ID = PT_STATUS.PRIMARY_DIAGNOSIS_ID)
AND (PTC_DIAGNOSIS.PATIENT_ID = PT_STATUS.PATIENT_ID)
LEFT OUTER JOIN C_DIAGNOSIS C_DIAGNOSIS ON
(C_DIAGNOSIS.DIAGNOSIS_ID = PTC_DIAGNOSIS.DIAGNOSIS_ID)
AND (C_DIAGNOSIS.DIAGNOSIS_SET_ID = PTC_DIAGNOSIS.DIAGNOSIS_SET_ID)
LEFT OUTER JOIN PTC_CERT_PERIOD PTC_CERT_PERIOD ON
(PTC_CERT_PERIOD.PATIENT_ID = PT_BASIC.PATIENT_ID)
AND (PTC_CERT_PERIOD.CERT_END_DATE > PT_BASIC.LAST_CERTIFICATION_DATE)
INNER JOIN O_DATASET O_DATASET ON
(O_DATASET.DATASET_ID = PT_BASIC.DATASET_ID)
LEFT OUTER JOIN PT_SERVICE PT_SERVICE ON
(PT_SERVICE.PATIENT_ID = PT_BASIC.PATIENT_ID)
LEFT OUTER JOIN PT_PAY_CONTROL PT_PAY_CONTROL ON
(PT_PAY_CONTROL.PATIENT_ID = PT_STATUS.PATIENT_ID)
AND (PT_PAY_CONTROL.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID)
LEFT OUTER JOIN PT_PAY_SOURCE PT_PAY_SOURCE ON
(PT_PAY_SOURCE.PATIENT_ID = PT_PAY_CONTROL.PATIENT_ID)
AND (PT_PAY_SOURCE.ADMIN_SET_ID = PT_PAY_CONTROL.ADMIN_SET_ID)
AND (PT_PAY_SOURCE.PAY_SOURCE_POINTER = PT_PAY_CONTROL.PRIMARY_POINTER)
LEFT OUTER JOIN A_INSURANCE_TYPE A_INSURANCE_TYPE ON
(A_INSURANCE_TYPE.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID)
AND (A_INSURANCE_TYPE.INSURANCE_CODE = PT_PAY_SOURCE.INSURANCE_CODE)
LEFT OUTER JOIN A_INSURANCE_COMPANY A_INSURANCE_COMPANY ON
(A_INSURANCE_COMPANY.COMPANY_CODE = PT_PAY_SOURCE.INSURANCE_COMPANY)
AND (A_INSURANCE_COMPANY.ADMIN_SET_ID = PT_PAY_SOURCE.ADMIN_SET_ID)
LEFT OUTER JOIN O_TEAM O_TEAM ON
(O_TEAM.TEAM_ID = PT_ADMISSION.TEAM_ID)
LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES ON
(VW_RESOURCES.RESOURCE_ID = PT_ADMISSION.REFERRAL_SOURCE)
LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES_2 ON
(VW_RESOURCES_2.RESOURCE_ID = PT_STATUS.ASSOCIATED_FACILITY_ID)
AND (VW_RESOURCES_2.ROLE_ID = PT_STATUS.ASSOCIATED_FACILITY_ROLE_ID)
WHERE
( O_DATASET.DATASET_NAME = 'AAA Hospice' )
AND ( PT_ADMISSION.ADMIT_DATE IS NOT NULL )
AND ( O_TEAM.DATASET_ID = O_Dataset.DATASET_ID )
AND ( PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL )
ORDER BY PT_BASIC.NAME_FULL

--SQL for PT_DEMOGRAPHIC
--This is the one joined to the above query in order to extract A_DEMOGRAPHIC.DESCRIPTION
SELECT PT_DEMOGRAPHIC.PATIENT_ID,
A_DEMOGRAPHIC.DESCRIPTION
FROM PT_DEMOGRAPHIC PT_DEMOGRAPHIC
LEFT OUTER JOIN A_DEMOGRAPHIC A_DEMOGRAPHIC ON
(A_DEMOGRAPHIC.DEMO_TYPE_ID = PT_DEMOGRAPHIC.DEMO_TYPE_ID)
AND (A_DEMOGRAPHIC.DEMO_ID = PT_DEMOGRAPHIC.DEMO_ID)
AND (A_DEMOGRAPHIC.ADMIN_SET_ID = PT_DEMOGRAPHIC.ADMIN_SET_ID)
WHERE ( PT_DEMOGRAPHIC.DEMO_TYPE_ID = 1 )

Parameters for Admissions Report
Dataset Name - O_Dataset - allow selection or all
TEAM_DESCRIPTION – A_DEMOGRAPHIC - allow selection or all
Admit Date - allow selection or all between @StartDate and @EndDate

Duane

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 04:01:47

Well....

The second sql brings back every PATIENT_ID in PT_DEMOGRAPHIC so that's obviously the link.

There's no reference to PT_DEMOGRAPHIC in the main query so....

I guess you can puzzle out whether there is a one to (at least) one relationship or not by doing this


SELECT COUNT(*)
FROM
    (
         SELECT
             pb.[patient_Id]
             , ptd.[patient_Id]
         FROM
             PT_BASIC pb
             LEFT JOIN PT_DEMOGRAPHIC ptd ON ptd.[patient_Id] = pb.[patient_Id]
    )
WHERE
    ptd.[patient_Id] IS NULL

If you get a number greater than 0 then you will definitely want to use a LEFT JOIN.

If you get 0 then you *MAY* be OK with an INNER JOIN but obviously I can't guarantee that not knowing the data model.

(this assumes that NULL is not a valid value for [patient_Id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-13 : 08:36:50

Thank you. I will try that.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources