Get all records from master table and related

Question

Post reply

Get all records from master table and related

amackay_1

Ten Centuries

Points: 1240
More actions
August 11, 2005 at 10:04 am

#88441

I've got 2 tables with the following structure:

CREATE TABLE [dbo].[family_history] (

[family_historyID] [int] IDENTITY (1, 1) NOT NULL ,

[intakeID] [int] NOT NULL ,

[family_history_conditionID] [int] NOT NULL ,

[family_members] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,

[other_info] [varchar] (150) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[family_history_conditions] (

[family_history_conditionID] [int] IDENTITY (1, 1) NOT NULL ,

[condition] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,

[activeYN] [bit] NOT NULL) ON [PRIMARY]

GO

ALTER TABLE [dbo].[family_history] WITH NOCHECK ADD

CONSTRAINT [PK_family_history] PRIMARY KEY CLUSTERED

(

  [family_historyID]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

ALTER TABLE [dbo].[family_history_conditions] WITH NOCHECK ADD

CONSTRAINT [PK_family_history_conditions] PRIMARY KEY CLUSTERED

(

  [family_history_conditionID]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

ALTER TABLE [dbo].[family_history] ADD

CONSTRAINT [FK_family_history_family_history_conditions] FOREIGN KEY

(

  [family_history_conditionID]) REFERENCES [dbo].[family_history_conditions] (

  [family_history_conditionID])

GO

I want to return all records from the family_history_conditions table and any related records in the family_history table for a particular user (unique intakeID). There is a user table that has the intakeID as the primary key as well if that helps formulate the query.

I tried using the following query but it drops records off from the family_history_conditions table if a different user has a match to that record in the family_history table.

SELECT FH.family_history_conditionID, FH.family_members, FH.other_info, FHC.condition FROM family_history FH RIGHT OUTER JOIN family_history_conditions FHC ON FH.family_history_conditionID=FHC.family_history_conditionID WHERE intakeID IS NULL OR intakeID=26

I know there is a different way to do this properly, but can't think of it at the moment. Thanks,

Andrew