"tho" <thorsten.br***@siemens.com> wrote in message
news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> Hi all,
> I'm searching for a simple sql statistic solution. I have 2 tables:
>
> 1st table:
> website-visits   |  department
> ----------------------------------
> 5    |  A&B CD Z1
> 30  |  A&B AD Z0
> 4    |  B&B X1
>
> 2nd table:
> customer     |  department-searchstring
> ----------------------------------
> Mr. X   | A&B
> Mrs. Y | B&B
>
> I need the following view:
> customer   |  website-visits
> ----------------------------------------
> Mr. X   |  35
> Mrs. Y |  4
>
> It's simple with only one search-string (LIKE) but I don't know how I
> can solve this problem.
>
> Thanx
>

> First, get all the individual records that match your criteria, then try to
> sum them after.
>
> This should match the customers to the departments/visits
>
> Select table1.[website-visits]
> , table1.department
> , table2.customer
> , table2.[department-searchstring]
> from table1
> inner join table2
> on table1.department like table2.[department-searchstring] + '%'
>
> if you need to find the search string anywhere in your department, rather
> than at the beginning change the last line to:
> on table1.department like '%' + table2.[department-searchstring] + '%'
>
> Now, just add in your sum on website visits.  We need to eliminate the
> columns that we don't want, and add a group by clause at the end of the
> query to summarize by those that we do want.
>
> Select sum(table1.[website-visits])
> --, table1.department
> , table2.customer
> --, table2.[department-searchstring]
> from table1
> inner join table2
> on table1.department like table2.[department-searchstring] + '%'
> group by table2.customer
>
> For more examples, check out a couple of websites on SQL.
>
> http://www.w3schools.com/sql/sql_intro.asp
>
> http://sqlzoo.net/
>
> "tho" <thorsten.br***@siemens.com> wrote in message
> news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> > Hi all,
> > I'm searching for a simple sql statistic solution. I have 2 tables:
> >
> > 1st table:
> > website-visits   |  department
> > ----------------------------------
> > 5    |  A&B CD Z1
> > 30  |  A&B AD Z0
> > 4    |  B&B X1
> >
> > 2nd table:
> > customer     |  department-searchstring
> > ----------------------------------
> > Mr. X   | A&B
> > Mrs. Y | B&B
> >
> > I need the following view:
> > customer   |  website-visits
> > ----------------------------------------
> > Mr. X   |  35
> > Mrs. Y |  4
> >
> > It's simple with only one search-string (LIKE) but I don't know how I
> > can solve this problem.
> >
> > Thanx
> >

"tho" <thorsten.br***@siemens.com> wrote in message
news:1158248567.498658.234040@p79g2000cwp.googlegroups.com...
> Hi Jim,
>
> thanks for your answer. I have written a smiliar solution but without
> success.
> I get this error:
> Server: Msg 446, Level 16, State 9, Line 3
> Cannot resolve collation conflict for like operation.
>
> (MS SQL 8.0)
>
> Any idea?
>
>
> bye
>
>
> Jim Underwood schrieb:
>
> > First, get all the individual records that match your criteria, then try
to
> > sum them after.
> >
> > This should match the customers to the departments/visits
> >
> > Select table1.[website-visits]
> > , table1.department
> > , table2.customer
> > , table2.[department-searchstring]
> > from table1
> > inner join table2
> > on table1.department like table2.[department-searchstring] + '%'
> >
> > if you need to find the search string anywhere in your department,
rather
> > than at the beginning change the last line to:
> > on table1.department like '%' + table2.[department-searchstring] + '%'
> >
> > Now, just add in your sum on website visits.  We need to eliminate the
> > columns that we don't want, and add a group by clause at the end of the
> > query to summarize by those that we do want.
> >
> > Select sum(table1.[website-visits])
> > --, table1.department
> > , table2.customer
> > --, table2.[department-searchstring]
> > from table1
> > inner join table2
> > on table1.department like table2.[department-searchstring] + '%'
> > group by table2.customer
> >
> > For more examples, check out a couple of websites on SQL.
> >
> > http://www.w3schools.com/sql/sql_intro.asp
> >
> > http://sqlzoo.net/
> >
> > "tho" <thorsten.br***@siemens.com> wrote in message
> > news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> > > Hi all,
> > > I'm searching for a simple sql statistic solution. I have 2 tables:
> > >
> > > 1st table:
> > > website-visits   |  department
> > > ----------------------------------
> > > 5    |  A&B CD Z1
> > > 30  |  A&B AD Z0
> > > 4    |  B&B X1
> > >
> > > 2nd table:
> > > customer     |  department-searchstring
> > > ----------------------------------
> > > Mr. X   | A&B
> > > Mrs. Y | B&B
> > >
> > > I need the following view:
> > > customer   |  website-visits
> > > ----------------------------------------
> > > Mr. X   |  35
> > > Mrs. Y |  4
> > >
> > > It's simple with only one search-string (LIKE) but I don't know how I
> > > can solve this problem.
> > >
> > > Thanx
> > >
>