inner join with 0 value

[Smarty]

Hey,
I solved my join problem with more tables with the sql statement at the bottom. Now i only need the fields [Company],[ID_Company] and [Company].[Company] in the select to replace the company id by the company name.

This is the problem: the ID_Company field in the Contacts table is most of the time empty. As a result of this, no records can be found. How do i do this so that the both fields are in my query when the ID_Company refers to a company that exists and don't get the fields (or empty fields) when the ID_Company is blank or refers to a company that doesn't exist without losing all my records?

I assume it is not that easy to understand my question, but tell me when something isn't clear...

This is the sql statement that works (without company):

SELECT [Contacts].[ID_Contact] AS Contacts_ID_Contact, [Contacts].[Contact_Name], [Contacts].[Contact_Tel], [Contacts].[Contact_Email], [Contacts].[ID_Company], [Contacts].[Contact_Commentaar], [Taken].[ID_Klant], [Taken].[ID_Opdracht], [Taken].[ID_Taak], [Taken].[Datum], [Taken].[ID_Personeel], [Taken].[ID_Contact] AS Taken_ID_Contact, [Taken].[ID_Taaktype], [Taken].[Samenvatting], [Taken].[Commentaar], [Taaktypes].[Taaktype], [Taaktypes].[Taaktype], [Personeel].[ID_Personeel], [Personeel].[initialen], [Klanten].[ID_Klant], [Klanten].[Klant], [Opdrachten].[ID_Opdracht], [Opdrachten].[Opdracht]
FROM Contacts INNER JOIN ((((Taken INNER JOIN Taaktypes ON [Taken].[ID_Taaktype]=[Taaktypes].[ID_Taaktype]) INNER JOIN Opdrachten ON ([Taken].[ID_Klant]=[Opdrachten].[ID_Klant]) AND ([Taken].[ID_Opdracht]=[Opdrachten].[ID_Opdracht])) INNER JOIN Klanten ON [Taken].[ID_Klant]=[Klanten].[ID_Klant]) INNER JOIN Personeel ON [Taken].[ID_Personeel]=[Personeel].[ID_Personeel]) ON [Contacts].[ID_Contact]=[Taken].[ID_Contact];

This is the sql statement that doesn't work (with company)

SELECT [Contacts].[ID_Contact] AS Contacts_ID_Contact, [Contacts].[Contact_Name], [Contacts].[Contact_Tel], [Contacts].[Contact_Email], [Contacts].[ID_Company], [Contacts].[Contact_Commentaar], [Taken].[ID_Klant], [Taken].[ID_Opdracht], [Taken].[ID_Taak], [Taken].[Datum], [Taken].[ID_Personeel], [Taken].[ID_Contact] AS Taken_ID_Contact, [Taken].[ID_Taaktype], [Taken].[Samenvatting], [Taken].[Commentaar], [Taaktypes].[Taaktype], [Taaktypes].[Taaktype], [Personeel].[ID_Personeel], [Personeel].[initialen], [Klanten].[ID_Klant], [Klanten].[Klant], [Opdrachten].[ID_Opdracht], [Opdrachten].[Opdracht], [Company].[ID_Company], [Company].[Company]
FROM (Contacts INNER JOIN ((((Taken INNER JOIN Taaktypes ON [Taken].[ID_Taaktype]=[Taaktypes].[ID_Taaktype]) INNER JOIN Opdrachten ON ([Taken].[ID_Klant]=[Opdrachten].[ID_Klant]) AND ([Taken].[ID_Opdracht]=[Opdrachten].[ID_Opdracht])) INNER JOIN Klanten ON [Taken].[ID_Klant]=[Klanten].[ID_Klant]) INNER JOIN Personeel ON [Taken].[ID_Personeel]=[Personeel].[ID_Personeel]) ON [Contacts].[ID_Contact]=[Taken].[ID_Contact]) INNER JOIN Company ON [Contacts].[ID_Company]=[Company].[ID_Company];

Thanx already...

 

[tlbroadbent]

You need to use an OUTER JOIN, in this case a LEFT JOIN.

SELECT [Contacts].[ID_Contact] AS Contacts_ID_Contact, [Contacts].[Contact_Name], [Contacts].[Contact_Tel], [Contacts].[Contact_Email], [Contacts].[ID_Company], [Contacts].[Contact_Commentaar], [Taken].[ID_Klant], [Taken].[ID_Opdracht], [Taken].[ID_Taak], [Taken].[Datum], [Taken].[ID_Personeel], [Taken].[ID_Contact] AS Taken_ID_Contact, [Taken].[ID_Taaktype], [Taken].[Samenvatting], [Taken].[Commentaar], [Taaktypes].[Taaktype], [Taaktypes].[Taaktype], [Personeel].[ID_Personeel], [Personeel].[initialen], [Klanten].[ID_Klant], [Klanten].[Klant], [Opdrachten].[ID_Opdracht], [Opdrachten].[Opdracht], [Company].[ID_Company], [Company].[Company]
FROM (Contacts INNER JOIN ((((Taken INNER JOIN Taaktypes ON [Taken].[ID_Taaktype]=[Taaktypes].[ID_Taaktype]) INNER JOIN Opdrachten ON ([Taken].[ID_Klant]=[Opdrachten].[ID_Klant]) AND ([Taken].[ID_Opdracht]=[Opdrachten].[ID_Opdracht])) INNER JOIN Klanten ON [Taken].[ID_Klant]=[Klanten].[ID_Klant]) INNER JOIN Personeel ON [Taken].[ID_Personeel]=[Personeel].[ID_Personeel]) ON [Contacts].[ID_Contact]=[Taken].[ID_Contact]) LEFT JOIN Company ON [Contacts].[ID_Company]=[Company].[ID_Company]; Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[Guest_imported]

Well, you helped me a lot with this answer!!!

Thanx!

Maarten