Help with SQL query

I have a ContactTypes table as follows:-
ContactTypeId int PK
ContactType varchar(50)
I have several different types of contact types (i.e. Invoice,
Business, Manager, Home, etc).
I have a Client table as follows:
ClienttId int PK
ClientName varchar(50)
I have a ContactAddresses table as follows:-
ContactAddressId int PK
ClientId
ContactTypeId
Address1
Address2
Town
Postcode
Can anyone tell me how i can write an SQL query to fetch all the
different types of contacts for a client, regardless if they exist or
not?
The query should return something like for a selected Client:-
Contact Type Address1 Address2 Town PostCode
Invoice 123 somewhere there AB12 C12
Business 456 overthere here CD34 E21
Manager
Home 987 here where D21 S12
So far i have come up with the following but it requires separate SQL
statements for each column. Can anyone recommend a way around this?
SELECT ContactType
,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
CA.ContactTypeId = CT.ContactTypeId)
,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
FROM ContactTypes CT
Thanks in advance for any suggestions.
DanI think you want a cross join to get all possible combinations of client and
contact type, and an outer join to get the addresses that exist, while still
keeping the contact type/client combinations that do not.
select c.clientname
, b.ContactType
, a.Address1
, a.Address2
, a.Town
, a.PostCode
from ContactTypeId b
cross join Client c
left outer join ContactAddresses a
on c.ClienttId = a.ClienttId
and c.ContactTypeID = b.ContactTypeID
<dan_williams@.newcross-nursing.com> wrote in message
news:1148503334.127146.20190@.i39g2000cwa.googlegroups.com...
> I have a ContactTypes table as follows:-
> ContactTypeId int PK
> ContactType varchar(50)
> I have several different types of contact types (i.e. Invoice,
> Business, Manager, Home, etc).
> I have a Client table as follows:
> ClienttId int PK
> ClientName varchar(50)
> I have a ContactAddresses table as follows:-
> ContactAddressId int PK
> ClientId
> ContactTypeId
> Address1
> Address2
> Town
> Postcode
>
> Can anyone tell me how i can write an SQL query to fetch all the
> different types of contacts for a client, regardless if they exist or
> not?
> The query should return something like for a selected Client:-
> Contact Type Address1 Address2 Town PostCode
> Invoice 123 somewhere there AB12 C12
> Business 456 overthere here CD34 E21
> Manager
> Home 987 here where D21 S12
> So far i have come up with the following but it requires separate SQL
> statements for each column. Can anyone recommend a way around this?
> SELECT ContactType
> ,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
> CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> FROM ContactTypes CT
> Thanks in advance for any suggestions.
> Dan
>

Help with SBE Business Contact Manager

I'm not sure if this is the correct place but I'm stuck!

I installed MS SBE 2003 w/ business contact manager and for about a week it worked well. Then funny things started happening. I got to the point where the business task menu pull down menu (from within Outlook 2003) was not even showing up! The contact manager that is under the same window as the regular contacts would only display the name of the contact but nothing else stating that the Business Contact Manager was unable to Initialize this Form. Ouch! That sucked.

So, I backed up everything I could think of (exported all bits and pieces of my Outlook to individual PST files and copied those to my external drive. Then I attempted to uninstall MSO SBE which worked sort of. It couldn't take out the SQL part and now even after a complete Norton Wipe, Disk Defrag and Registry Repair it still doesn't uninstall or reinstall properly. All other aspects of MSO SBE work except for the part that I bought it for the use of the database.

Could someone please assist or direct me to where I might be able to get some help - I don't know much VB but can find what I need with some help.

Anything is better than where I am now! Please help if you can.

Thanks!!

I also am experiencing this problem any help would be greatly appreciated.|||I'm having the same problem ...please someone out there HELP ......|||

I just found out how to fix this... open outlook...select help... then select about ms office outlook... select disabled items once you enable the BCM close outlook and open it again... voila is that simple...

|||I am getting the "Outlook business manager was unable to initialize this form" message but it's only happening when I try to design the form. I have SP2 installed, I am not importing or copying any BCM stuff. It's all new. BCM doesn't not show as being disabled. Any one have an explaination?

Help with SBE Business Contact Manager

I'm not sure if this is the correct place but I'm stuck!

I installed MS SBE 2003 w/ business contact manager and for about a week it worked well. Then funny things started happening. I got to the point where the business task menu pull down menu (from within Outlook 2003) was not even showing up! The contact manager that is under the same window as the regular contacts would only display the name of the contact but nothing else stating that the Business Contact Manager was unable to Initialize this Form. Ouch! That sucked.

So, I backed up everything I could think of (exported all bits and pieces of my Outlook to individual PST files and copied those to my external drive. Then I attempted to uninstall MSO SBE which worked sort of. It couldn't take out the SQL part and now even after a complete Norton Wipe, Disk Defrag and Registry Repair it still doesn't uninstall or reinstall properly. All other aspects of MSO SBE work except for the part that I bought it for the use of the database.

Could someone please assist or direct me to where I might be able to get some help - I don't know much VB but can find what I need with some help.

Anything is better than where I am now! Please help if you can.

Thanks!!

I also am experiencing this problem any help would be greatly appreciated.|||I'm having the same problem ...please someone out there HELP ......|||

I just found out how to fix this... open outlook...select help... then select about ms office outlook... select disabled items once you enable the BCM close outlook and open it again... voila is that simple...

|||I am getting the "Outlook business manager was unable to initialize this form" message but it's only happening when I try to design the form. I have SP2 installed, I am not importing or copying any BCM stuff. It's all new. BCM doesn't not show as being disabled. Any one have an explaination?