Search query between two tables

[Sorce]

I'm working on search query for a troubleticket system.

There are two tables I want to search, the description in the tickets table
and the corresponding notes in the notes table. The problem is there is a
one to many relationship between the tickets (one) and the notes (many)
tables.

I only need the ticket number of any ticket that finds the search string in
the description or any of the corresponding notes.

[Erland Sommarskog]

[posted and mailed, please reply in news]

Sorce (kp*****@ntihc.com) writes:

I'm working on search query for a troubleticket system.

There are two tables I want to search, the description in the tickets
table and the corresponding notes in the notes table. The problem is
there is a one to many relationship between the tickets (one) and the
notes (many) tables.

I only need the ticket number of any ticket that finds the search string
in the description or any of the corresponding notes.

SELECT ticketno, yadayada
FROM tickets t
WHERE t.description LIKE @searchstr
OR EXISTS (SELECT *
FROM notes n
WHERE n.ticketno = t.ticketno
AND n.comment LIKE @searchstr)

Note: if you in your posting include the following:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o The desired output given the sample.

You will get a tested answer. The above is just a guess on how your
tables may look like.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Sorce]

Thanks, that is exactly what I needed.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

[posted and mailed, please reply in news]

Sorce (kp*****@ntihc.com) writes:

I'm working on search query for a troubleticket system.

There are two tables I want to search, the description in the tickets
table and the corresponding notes in the notes table. The problem is
there is a one to many relationship between the tickets (one) and the
notes (many) tables.

I only need the ticket number of any ticket that finds the search string
in the description or any of the corresponding notes.

SELECT ticketno, yadayada
FROM tickets t
WHERE t.description LIKE @searchstr
OR EXISTS (SELECT *
FROM notes n
WHERE n.ticketno = t.ticketno
AND n.comment LIKE @searchstr)

Note: if you in your posting include the following:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o The desired output given the sample.

You will get a tested answer. The above is just a guess on how your
tables may look like.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp