Query using multiple tables

Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.

I have 3 tablesClients <-ooClientContacts oo->Contacts
(the <-oo means one to may relation between the tables)

A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.

My goal is to run a query which will return

Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
WHERE (Clients.WorkOnHold = 0)

The result should return values for
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category,
and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.

I tryed an INNER JOIN but it will return juts the records having contact information.

Any solutions are appreciated.
Thanks.

Clients

CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100),
[MailingStreet] [varchar] (50),
[MailingCity] [varchar] (35),
[MailingState] [varchar] (35) ,
[MailingZip] [varchar] (10),
[WorkOnHold] [bit] NULL ,
[ClientNotes] [varchar] (500),
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Contacts

CREATE TABLE [Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[JobTitle] [varchar] (50),
[BusinessStreet] [varchar] (50),
[BusinessCity] [varchar] (35),
[BusinessState] [varchar] (35),
[BusinessPhone] [varchar] (20),
[BusinessFax] [varchar] (20),
[E-mailAddress] [varchar] (255),
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ClientContacts

CREATE TABLE [ClientContacts] (
[ClientID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[Category] [varchar] (50),
CONSTRAINT [FK_ClientContacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_ClientContacts_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ContactID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

The INNER JOIN I tryed but is not good. It returns just clients having contacts attached.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName,
 Contacts.[E-mailAddress]
FROM ClientContacts INNER JOIN
 Clients ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

You will need a to use LEFT OUTER JOIN from Clients to the other tables:

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROMClients LEFT OUTER JOIN
 ClientContacts ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

|||Thanks for the attempt.
I tried it and it is not good !
It still returns only Clients having Contacts attached.

Any other solutions?
Thanks.|||I think I found the solution.
The first join has to be a LEFT OUTER JOIN (as ehorn pointed), but the second one should be a FULL OUTER JOIN.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState,
 Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROM Clients
LEFT OUTER JOIN ClientContacts ON ClientContacts.ClientID = Clients.ClientID
FULL OUTER JOIN Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold=0)

This code seems to work fine. I hope it is not just a coincidence the fact I get the result I expected.

Thanks.