Hi All
I have two tables CustomerDetails and CustomerAccounts
CustomerDetails
CustomerId
Name
Address
Town
Postcode
Email
SearchDetails
CustomerAccounts (Many)
Accountid
CustomerId
AccountNumber
CreateDate
Etc
Currently the CustomerDetails.SearchDetails column is a Computed column
which holds all of the address / name / email data which has been Trimmed and
uppered.
In my search for a customer screen a user can search for a whole or partial
words and I generate a simple select statement
SELECT * FROM CUSTOMER DETAILS WHERE SEARCHDETAILS LIKE 'XXX'
a list of possible matches is then displayed to the user. This works fine.
I now have to add a list of accountid's / account numbers from the related
CustomerAccounts table. So a use could type in a partial name and account
number. I can't display multiple rows per account number.
So I'd like my CustomerDetails.SearchDetails tO contain the value
'DAVIDDASHERADDRESS1ADDRESS2TOWNPOSTCODEDAVIDDASHER@.EMAIL.COM ACCOUNTNUMBER1
ACCOUNTNUMBER2'
I know this is possible in Oracle, just not too sure about my options in sql
server 2005
Any help would be most appreciated
Regards
DavidDavid
create table CustomerDetails
(CustomerId int,
[name] varchar(50),
address varchar(50),
email varchar(50),
SearchDetails as address +','+email)
insert into CustomerDetails (CustomerId,[name],address,email)
values (100,'John Smith','5 street','john@.hotmail.com')
insert into CustomerDetails (CustomerId,[name],address,email)
values (200,'David Blant','15 street','david@.gmail.com')
Create table CustomerAccounts
(
Accountid int,
CustomerId int
)
insert into CustomerAccounts values (1,100)
insert into CustomerAccounts values (2,100)
insert into CustomerAccounts values (1,200)
insert into CustomerAccounts values (2,200)
insert into CustomerAccounts values (3,200)
select SearchDetails from CustomerDetails where SearchDetails like
'%hotmail%'
union all
select cast(Accountid as varchar(50))Accountid
from CustomerAccounts ca join CustomerDetails cd on
ca.CustomerId=cd.CustomerId
where SearchDetails like '%hotmail%'
"David Dasher" <DavidDasher@.discussions.microsoft.com> wrote in message
news:E81751DC-D736-4006-AD2B-11FDB6ADB5F3@.microsoft.com...
> Hi All
> I have two tables CustomerDetails and CustomerAccounts
> CustomerDetails
> CustomerId
> Name
> Address
> Town
> Postcode
> Email
> SearchDetails
> CustomerAccounts (Many)
> Accountid
> CustomerId
> AccountNumber
> CreateDate
> Etc
> Currently the CustomerDetails.SearchDetails column is a Computed column
> which holds all of the address / name / email data which has been Trimmed
> and
> uppered.
> In my search for a customer screen a user can search for a whole or
> partial
> words and I generate a simple select statement
> SELECT * FROM CUSTOMER DETAILS WHERE SEARCHDETAILS LIKE 'XXX'
> a list of possible matches is then displayed to the user. This works fine.
> I now have to add a list of accountid's / account numbers from the related
> CustomerAccounts table. So a use could type in a partial name and account
> number. I can't display multiple rows per account number.
> So I'd like my CustomerDetails.SearchDetails tO contain the value
> 'DAVIDDASHERADDRESS1ADDRESS2TOWNPOSTCODEDAVIDDASHER@.EMAIL.COM
> ACCOUNTNUMBER1
> ACCOUNTNUMBER2'
> I know this is possible in Oracle, just not too sure about my options in
> sql
> server 2005
> Any help would be most appreciated
> Regards
>
> David|||Hi Uri
Thanks for your help.
That's great
David
"Uri Dimant" wrote:
> David
> create table CustomerDetails
> (CustomerId int,
> [name] varchar(50),
> address varchar(50),
> email varchar(50),
> SearchDetails as address +','+email)
> insert into CustomerDetails (CustomerId,[name],address,email)
> values (100,'John Smith','5 street','john@.hotmail.com')
> insert into CustomerDetails (CustomerId,[name],address,email)
> values (200,'David Blant','15 street','david@.gmail.com')
>
> Create table CustomerAccounts
> (
> Accountid int,
> CustomerId int
> )
> insert into CustomerAccounts values (1,100)
> insert into CustomerAccounts values (2,100)
> insert into CustomerAccounts values (1,200)
> insert into CustomerAccounts values (2,200)
> insert into CustomerAccounts values (3,200)
>
>
> select SearchDetails from CustomerDetails where SearchDetails like
> '%hotmail%'
> union all
> select cast(Accountid as varchar(50))Accountid
> from CustomerAccounts ca join CustomerDetails cd on
> ca.CustomerId=cd.CustomerId
> where SearchDetails like '%hotmail%'
>
>
> "David Dasher" <DavidDasher@.discussions.microsoft.com> wrote in message
> news:E81751DC-D736-4006-AD2B-11FDB6ADB5F3@.microsoft.com...
> > Hi All
> >
> > I have two tables CustomerDetails and CustomerAccounts
> >
> > CustomerDetails
> > CustomerId
> > Name
> > Address
> > Town
> > Postcode
> > Email
> > SearchDetails
> >
> > CustomerAccounts (Many)
> > Accountid
> > CustomerId
> > AccountNumber
> > CreateDate
> > Etc
> >
> > Currently the CustomerDetails.SearchDetails column is a Computed column
> > which holds all of the address / name / email data which has been Trimmed
> > and
> > uppered.
> >
> > In my search for a customer screen a user can search for a whole or
> > partial
> > words and I generate a simple select statement
> >
> > SELECT * FROM CUSTOMER DETAILS WHERE SEARCHDETAILS LIKE 'XXX'
> >
> > a list of possible matches is then displayed to the user. This works fine.
> >
> > I now have to add a list of accountid's / account numbers from the related
> > CustomerAccounts table. So a use could type in a partial name and account
> > number. I can't display multiple rows per account number.
> >
> > So I'd like my CustomerDetails.SearchDetails tO contain the value
> >
> > 'DAVIDDASHERADDRESS1ADDRESS2TOWNPOSTCODEDAVIDDASHER@.EMAIL.COM
> > ACCOUNTNUMBER1
> > ACCOUNTNUMBER2'
> >
> > I know this is possible in Oracle, just not too sure about my options in
> > sql
> > server 2005
> >
> > Any help would be most appreciated
> >
> > Regards
> >
> >
> > David
>
>
