Outer Join issues

[Mustafa Ismail Mustafa]

I have two tables that share two columns: Table 1: Name: EMR.mmPatientAdmissionSymptoms Columns: PatientID, AdmittanceID,SymptomID Table 2: Name: EMR.mmPatientAdmissionSymptomsOther Columns: PatientID, AdmittanceID,Symptom EMR.mmPatientAdmissionSymptoms.SymptomID is an integer whilst EMR.mmPatientAdmissionSymptomsOther.Symptom is nvarchar(MAX). Now, I want to pull all the data in both tables, where the data in either table not existing being null. That should be a straight full outer join, right?

SELECT
pas.PatientID, pas.AdmittanceID, pas.SymptomID, paso.Symptom
FROM EMR.mmPatientAdmissionSymptoms as pas full outer join
EMR.mmPatientAdmissionSymptomsOther as paso
on pas.PatientID = paso.PatientID
AND pas.AdmittanceID = paso.AdmittanceID

But it doesn't; It seems to preform a cross join. Out of frustration I tried left, right, cross and inner joins just to see if I'm on the right track or not. They ALL give the same result Scripts if you want to try this: (altered slightly so you don't have to remove the FK and Schemas)

-- Table 1
CREATE TABLE [mmPatientAdmissionSymptoms](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[SymptomID] [int] NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptoms] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[SymptomID] ASC
)

--Table 2
CREATE TABLE [EMR].[mmPatientAdmissionSymptomsOther](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Symptom] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptomsOther] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[ID] ASC
)
--ID is an Identity field so that I can differentiate between one entry and the other for each patient at each admittance

Results are returned as: PatientID AdmittanceID SymptomID Symptom ============================================================ 1234 1 1 This is a symptom 1234 1 3 This is a symptom Table 1: PatientID AdmittanceID SymptomID ============================================== 1234 1 1 1234 1 3 Table 2: PatientID AdmittanceID ID Symptom ==================================================================== 1234

[leckey 0]

Are you wanting to relate/compare the tables or are you in the end trying to merge the tables?

Back in the blog beatch! http://CraptasticNation.blogspot.com/[^]

[Mustafa Ismail Mustafa]

More like merge them but with the repeated columns removed (PatientID & AdmittanceID). In a way, I'd want results of the query to be as such: (based on the tables given in the OP) (Table1) (Table1) (Table1) (Table2) PatientID AdmittanceID SymptomID Symptom ============================================================ 1234 1 1 NULL 1234 1 3 NULL 1234 1 NULL This is a symptom Makes sense? [Edit] I just realized that the tables aren't very clear. Try this: Table 1

PatientID

AdmittanceID

SymptomID

1234

1

1

1234

1

3

Table 2

PatientID

AdmittanceID

ID

Symptom

1234

1

1

This is a symptom

Wanted result

PatientID

AdmittanceID

SymptomID

Symptom

1234

1

1

NULL

1234

1

3

NULL

1234

1

NULL

This is a symptom

What I get:

PatientID

AdmittanceID

SymptomID

Symptom

1234

1

1

This is a symptom

1234

1

3

This is a symptom

If the post was helpful, please vote, eh! Current activities: Book: Devils by Fyodor Dostoyevsky Project: Hospital Automation, final stage Learning: Image analysis, LINQ Now and forever, defiant to the end. What is Multiple Sclerosis[^]?

[DoctorMick]

Would a union not make more sense than a join for this scenario?

[i j russell]

You can't use an outer join in this scenario because outer joins return nulls where there is not a match in the other table; You have matches. A union query would be more appropriate in this case.

[leckey 0]

I agree about the union. This is a link I had bookmarked that might help. http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx[^]

Back in the blog beatch! http://CraptasticNation.blogspot.com/[^]

[Mustafa Ismail Mustafa]

I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time! Thanks :)

If the post was helpful, please vote, eh! Current activities: Book: Devils by Fyodor Dostoyevsky Project: Hospital Automation, final stage Learning: Image analysis, LINQ Now and forever, defiant to the end. What is Multiple Sclerosis[^]?

[Mustafa Ismail Mustafa]

I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time! Thanks for the explanation though. But I thought Outer joins will return nulls because there was no corresponding columns that had a value (of course, now that I type this I remember that all this is row based).

If the post was helpful, please vote, eh! Current activities: Book: Devils by Fyodor Dostoyevsky Project: Hospital Automation, final stage Learning: Image analysis, LINQ Now and forever, defiant to the end. What is Multiple Sclerosis[^]?