[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.

Re: Join on Key or Null

You could use an Outer Join to the last table

Re: Join on Key or Null

Quote:

Originally Posted by GaryMazzone

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.

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')