Searching For text in a self join

8 Dec 2005 5:06 PM

Ben

Hi

I have these tables:

Document Table
DocumentID PK
.......

List Table
ID PK
DocumentID FK
Customer
Notes

For each Document Table record there can be up to 6 List Table records.

Return List Records where for each instance of a Document one List.Notes
record contains the text"sheperd" and another List.Notes contains the text
"Osmont".

I am stumpted, any help would be much apprecited.

Thanks
B

8 Dec 2005 5:24 PM

Steve Kass

Ben,

This is a little different than the typical "relational division"
problem, since it sounds like one row could contain both Osmont
and sheperd. You will find solutions to similar problems if you
search groups.google.news for
"relational divison" sqlserver
but for this particular problem, you might try:

select ID, min(DocumentID) as DocumentID from (
select 'Osmont' as tag, ID, DocumentID
from List
where Notes like '%Osmont%'
union all
select 'sheperd' as tag, ID, DocumentID
from List
where Notes like '%sheperd%'
) as Matches
group by ID
having min(tag) <> max(tag)
-- or to generalize: having count(distinct tag) = 2

As far as I can tell, you don't need the Document table,
and I'm not sure what you want the results to show, so I've
guessed.

If you want more information, post the CREATE TABLE statements
for the tables, INSERT statements with sample data, and your expected
results, for a non-trivial set of data.

Steve Kass
Drew University

Ben wrote:

Show quote

>Hi
>
>I have these tables:
>
>Document Table
>DocumentID PK
>......
>
>List Table
>ID PK
>DocumentID FK
>Customer
>Notes
>
>
>For each Document Table record there can be up to 6 List Table records.
>
>Return List Records where for each instance of a Document one List.Notes
>record contains the text"sheperd" and another List.Notes contains the text
>"Osmont".
>
>I am stumpted, any help would be much apprecited.
>
>Thanks
>B
>
>
>
>

9 Dec 2005 2:35 PM

Ben

Thanks Steve

That worked perfectly.

Regards
B
Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:uPiKRxB$FHA.360@TK2MSFTNGP09.phx.gbl...
> Ben,
>
> This is a little different than the typical "relational division"
> problem, since it sounds like one row could contain both Osmont
> and sheperd. You will find solutions to similar problems if you
> search groups.google.news for
>   "relational divison" sqlserver
> but for this particular problem, you might try:
>
> select ID, min(DocumentID) as DocumentID from (
>   select 'Osmont' as tag, ID, DocumentID
>   from List
>   where Notes like '%Osmont%'
>   union all
>   select 'sheperd' as tag, ID, DocumentID
>   from List
>   where Notes like '%sheperd%'
> ) as Matches
> group by ID
> having min(tag) <> max(tag)
> -- or to generalize: having count(distinct tag) = 2
>
> As far as I can tell, you don't need the Document table,
> and I'm not sure what you want the results to show, so I've
> guessed.
>
> If you want more information, post the CREATE TABLE statements
> for the tables, INSERT statements with sample data, and your expected
> results, for a non-trivial set of data.
>
> Steve Kass
> Drew University
>
> Ben wrote:
>
> >Hi
> >
> >I have these tables:
> >
> >Document Table
> >DocumentID PK
> >......
> >
> >List Table
> >ID PK
> >DocumentID FK
> >Customer
> >Notes
> >
> >
> >For each Document Table record there can be up to 6 List Table records.
> >
> >Return List Records where for each instance of a Document one List.Notes
> >record contains the text"sheperd" and another List.Notes contains the
text
> >"Osmont".
> >
> >I am stumpted, any help would be much apprecited.
> >
> >Thanks
> >B
> >
> >
> >
> >