By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Search query between two tables

P: n/a
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.
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
[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
Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.