-
Mar 3rd, 2010, 02:27 PM
#1
[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.
-
Mar 3rd, 2010, 02:30 PM
#2
Re: Join on Key or Null
You could use an Outer Join to the last table
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Mar 3rd, 2010, 02:37 PM
#3
Re: Join on Key or Null
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.
-
Mar 3rd, 2010, 03:12 PM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|