473,573 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Search query between two tables

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
2 3398
[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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thanks, that is exactly what I needed.

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.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****@sommarsk og.se

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

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2259
by: Zaphod Beeblebrox | last post by:
As much of this question relates to mysql, it may be OT? I'm trying to make a search engine for a reasonably complex database that was originally developed by someone else in Access. I've ported the data to mySQL and am reasonably happy with everything except the performance I'm getting from queries with multpile joins. Although the...
5
8997
by: LRW | last post by:
Well, I have another question that's hard to ask, so I may end up over explaining the question. I have about 40 tables, all with a similar column structure. I want to be able to search through every table for a keyword. Is there a way to do that using, an array or next or something. So like, all the table, say tbl_1, tbl_2, tbl_3 etc have...
11
3784
by: Adrian Parker | last post by:
I have a database of 200+ tables (two tables per school), each with 100 - 4000 records (one record per student). A contract I'm looking at wants to be able to do a search across all the tables, searching for values a user inputs via a Search form. I have a solution, but I don't think it'll work on large scales. Is there a better bet then...
0
1419
by: Robert Oschler | last post by:
I read a while back that MySQL will only use one index per query. (If this is not so, please tell me and point me to a doc that gives a good explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x. Here's my dilemma. 1) --------- I have two tables that have records with a FULLTEXT index text field in each of them. ...
2
2397
by: TH | last post by:
I am (still :) working on a recipe database. Now I am trying to figure out how to set it up for an ingredient search. What I want it to be able to do is to search by one ingredient, sometimes by two, and sometimes by three. There won't always be a second and third ingredient to search on. It will depend on how the user wants to search. ...
3
1790
by: Jan Szymczuk | last post by:
I am trying to create a query that will show me who is phoning who in an organisation from available Telephone Billing information. I am creating a MSAccess 2000 database with a few few tables, two of which are: TableMembers: (containg fields Refs, DateCreated, MembershipNo, OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)...
0
2067
by: | last post by:
I have a question about spawning and displaying subordinate list controls within a list control. I'm also interested in feedback about the design of my search application. Lots of code is at the end of this message, but I will start with an overview of the problem. I've made a content management solution for my work with a decently...
5
3361
by: kanley | last post by:
I have a main table with a text description field. In this field, its populated with a string of data. I need to identify from this string of data the name of the vendor using some keywords. I have another table that contains 2 fields, one being the keywords to search for from the string of data and the other field being the vendor name...
0
2714
by: JamesOo | last post by:
I have the code below, but I need to make it searchable in query table, below code only allowed seach the table which in show mdb only. (i.e. have 3 table, but only can search either one only, cannot serch by combine 3 table) Example I have the query table below, how do I make the code to seach based on the query from this: SELECT...
0
7983
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8179
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8035
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6356
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3694
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2166
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
992
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.