SQL query

[lothadio]

I have this table

link_table

document_number |link_doc_ |link_doc_2 |link_doc_3 |link_doc_4 |link_doc_5
*AM0101 | AM0101 | CS0101 | AM0103 | AM0111 | AM0151
*AM0102 | CS0101 | AM0104 | PP0101 | RR0101 | AM9898



I want my SQL statement to return all document_number fields that have AM0101 in one of
the Link_doc_$ fields

SELECT document_number
FROM link_table
WHERE ? = AM0101

The result for this example table would be:

*AM0101



So basiscally the SQL would search through all the link_doc_$ fileds and return the document
number is criteria was met

 

[tlbroadbent]

This probably the best way to what you wnat.

SELECT document_number
FROM link_table
WHERE link_doc_1 = 'AM0101'
OR link_doc_2 = 'AM0101'
OR link_doc_3 = 'AM0101'
OR link_doc_4 = 'AM0101'
OR link_doc_5 = 'AM0101'

You could do it this way, also. However, it will be less efficient.

SELECT document_number
FROM link_table
WHERE (link_doc_1 + link_doc_2 + link_doc_3 + link_doc_4 + link_doc_5) Like '%AM0101%'

You could also devise other ways to build the query in a stored procedure but that doesn't appear to be worth the effort except as a learning experience. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[lothadio]

so if there is more than one result from this query will it create an array? and if so how do I extract the array?

 

[tlbroadbent]

It will create a recordset. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris