Post reply

Get all records from master table and related

  • 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

  • August 11, 2005 at 10:18 am

    #581469

    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 and FH.intakeID=26

     


    * Noel

  • August 11, 2005 at 10:37 am

    #581477

    Thanks Noel, that's what I was looking for!

  • August 11, 2005 at 10:41 am

    #581485

    My pleasure!

     


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply