?
Solved

Find records impacted by more than 1 person?

Posted on 2011-09-14
4
Medium Priority
?
334 Views
Last Modified: 2012-05-12
Sorry if the title is vague. I couldn't think of a better way to describe
what I'm trying to do.

I have a table that has the following five fields:

COMMENT_SYS_ID,
ASSET_ID,
WORKER_NAME,
COMMENT_DATE,
COMMENT

I want to know what all ASSET_ID's have comments
from both '%JIM'%' and '%NANCY%'. How to do this easily?
0
Comment
Question by:JustinW
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36537096
What database?  This was posted in three different zones.

In Oracle and probably some of the others:

select asset_id from table where comment is not null and worker_name like '%JIM%'
intersect
select asset_id from table where comment is not null and worker_name like '%NANCY%'
0
 
LVL 1

Author Comment

by:JustinW
ID: 36537136
Oracle in this case.
Worked perfect, thanks a tonne!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36537147
may not be the most efficient but should be portable to just about any SQL based platform


select asset_id from yourtable
where worker_name like '%JIM%'
and asset_id in (select asset_id from yourtable where worker_name like '%NANCY%')
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36537149
>>Worked perfect, thanks a tonne!

Glad to help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question