SQL Multiple Instance Problem

Ok, the problem I'm having is that I'm trying to pull out an entry that satisfies certain conditions, but they can only be satisfied across multiple entries... Does that make sense? I doubt it so here is what I'm working with...

Field1 | Field2
---------------
test1 | one
test1 | two
test1 | three
test2 | one
test2 | three

If( Field2 == one AND Field2 == two ) Then
return Field1

In this case I would hope to get test1.

Something like that. So I want my query to find the entry in field1 that satisfies both constraints found in field2. Sorry if this sounds confusing, I'm getting more confused as I write it!!

You database isn't designed properly.

1) You're storing duplicate information, which you shouldn't do
2) You can't return your results in that manner because they aren't associated with the same record.

You should put some more thought into your database design first.

The premise of this design is from a Document Management System. I use one table to hold my document entries and another to hold field values (for searching purposes). The example above was a joined table, here is the tables beforehand:

Document Table

id | name
----------
1 | test1
2 | test2

Document Fields

id | document_id | value
----------
1 | 1 | one
2 | 1 | two
3 | 1 | three
4 | 2 | one
5 | 2 | three

Any suggestions on how I might be able to improve on this design?

What's the purpose of the multiple values?
You can't return the records the way you want unless the values are associated with the same row.

The multiple values are to identify documents. Such as Plant, Department, Process, Number, etc. These are attributes for the documents and was designed this way so that attributes can be added/deleted easier by adding/deleting rows instead of columns.

The way your database is designed, you can't return the results your want.
You can't do

because it is looking at the row, rather than the column and none of the rows have 'one' and 'two' in them

You can't do

because then it will pull test2 also, because it has a 'one' in Field2.

You should consider redesigning your database structure.

You could try creating a cursor to loop through the records and return only those records where the docID is the same and field2 contains 'one' and 'two'.

Just thought I'd follow up to give a resolution to this thread.

Since I am working with someone elses design and fitting it for my needs, restructuring the tables in an easier to use format appeared to be too time consuming as many things worked off of this structure. Since I could not fit a restructure in my time table, I created a table just to fullfill my current needs.

Using a series of joins, I joined the same table multiple times so I could populate a single row with the information I needed.

For example:

Document Table

id | name
----------
1 | test1
2 | test2

Document Fields

id | document_id | document_field_id | value
----------
1 | 1 | 1 | one
2 | 1 | 2 | two
3 | 1 | 3 | three
4 | 2 | 1 | one
5 | 2 | 3 | three



This way I can end up with a table where all fields in one category will be in one column matched with the appropriate document. With a table like this it is much simpler to search for things.

Thanks for the help,
DBAC