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 tables Clients <-oo ClientContacts 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)