Results 1 to 4 of 4

Thread: [RESOLVED] Join on Key or Null

  1. #1

    Thread Starter
    PowerPoster kfcSmitty's Avatar
    Join Date
    May 2005
    Posts
    2,248

    Resolved [RESOLVED] Join on Key or Null

    I have a query where I want to pull out the Name, Company, Last Contact Date and Type of Programming from several different tables in the database.

    Name and company are in the same table and are both the Name field. Contact_Number of 0 is a company, any other Contact_Number is a person.

    The user must have a Last Contact Date in order to be included in the query, so I have 3 joins to get the required information from the 3 tables.

    Now where I am getting stuck is that I need to do another join to get the Type of Programming field.

    The table that holds the Type of Programming field shares a ClientID with my main table, so the relationship is there, when the Type of Programming value has been entered.

    My problem lies with the fact that whether or not the user has a Type of Programming field, I still want them in my query.

    I'm going to re-read this and possibly add more information in case it doesn't make any sense.

    The condensed version: I need the Type of Programming field to display whether there is an entry for it or not. The only way I know to display a field is through a join, but you can't join on nothing as far as I am aware.

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Join on Key or Null

    You could use an Outer Join to the last table
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    PowerPoster kfcSmitty's Avatar
    Join Date
    May 2005
    Posts
    2,248

    Re: Join on Key or Null

    Quote Originally Posted by GaryMazzone View Post
    You could use an Outer Join to the last table
    I'm using nothing but outer joins. Here is my query, which works fine unless there is no Type of Programming.

    Note that this is a little different than I explained from before, but the only real difference is that I am using a type_Id to associate with the Type of Programming field, and the client_id to associate with the table that I get the type_id from.

    Code:
    SELECT     a.Name, b.Name AS Company, d.DateCol AS [Last Contact], e.Description AS [Type of Programming]
    FROM         AMGR_Client_Tbl AS a LEFT OUTER JOIN
                          AMGR_Client_Tbl AS b ON a.Client_Id = b.Client_Id LEFT OUTER JOIN
                          AMGR_Notes_Tbl AS d ON a.Client_Id = d.Client_Id LEFT OUTER JOIN
                          AMGR_User_Field_Defs_Tbl AS e ON a.Client_Id =
                              (SELECT     Client_Id
                                FROM          AMGR_User_Fields_Tbl AS x
                                WHERE      (Type_Id IN
                                                           (SELECT     Type_Id
                                                             FROM          AMGR_User_Field_Defs_Tbl AS z
                                                             WHERE      (Type_Id = '2') AND (Code_Id <> '0'))) AND (Client_Id = a.Client_Id))
    WHERE     (d.Note_Type =
                              (SELECT     Note_Type
                                FROM          AMGR_Notes_Tbl AS c
                                WHERE      (Client_Id = a.Client_Id) AND (Contact_Number = a.Contact_Number) AND (Note_Type = 'Contact') AND (DATEDIFF(day, DateCol, 
                                                       '02/01/2010 12:00:00 AM') <= 0) AND (DATEDIFF(day, DateCol, '03/30/2010 12:00:00 AM') >= 0))) AND (b.Record_Type = '1') AND 
                          (e.Code_Id <> '0') AND (e.Type_Id = '2') AND (e.Code_Id IN
                              (SELECT     Code_Id
                                FROM          AMGR_User_Fields_Tbl
                                WHERE      (Type_Id = '2') AND (Client_Id = a.Client_Id))) AND (d.Contact_Number = a.Contact_Number)
    I can put an ERD up if it would help someone help me.

  4. #4

    Thread Starter
    PowerPoster kfcSmitty's Avatar
    Join Date
    May 2005
    Posts
    2,248

    Re: Join on Key or Null

    Figured it out. I switched everything to inner joins except the Note one and re-did the entire query. It is working now.

    For anyone that cares, the new query looks like

    Code:
    SELECT     Client.Name, Company.Name AS [Company Name], Note.DateCol, UDF.Description
    FROM         AMGR_Client_Tbl AS Client INNER JOIN
                          AMGR_Client_Tbl AS Company ON Client.Client_Id = Company.Client_Id INNER JOIN
                          AMGR_Notes_Tbl AS Note ON Client.Contact_Number = Note.Contact_Number AND Client.Client_Id =
                              (SELECT     TOP (1) Client_Id
                                FROM          AMGR_Notes_Tbl
                                WHERE      (Client_Id = Note.Client_Id) AND (Contact_Number = Note.Contact_Number) AND (Note_Type = 'Contact')) LEFT OUTER JOIN
                          AMGR_User_Field_Defs_Tbl AS UDF ON UDF.Type_Id = '2' AND UDF.Code_Id =
                              (SELECT     Code_Id
                                FROM          AMGR_User_Fields_Tbl
                                WHERE      (Type_Id = '2') AND (Client_Id = Client.Client_Id) AND (Contact_Number = Client.Contact_Number))
    WHERE     (Client.Contact_Number <> 0) AND (Company.Contact_Number = 0) AND (Note.Note_Type = 'Contact')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width