SQL SELECT FROM: Inquiry

I have a table with multipl开发者_高级运维e fields for address, such as address_line_1, address_line_2, etc.

Is there any way that I can use a WHERE clause across multiple fields at once, without having to use an OR/AND statement?

For example:

SELECT * 
  FROM FIN_LIVE.CUSTOMER_ADDRESSES 
 WHERE SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3 = 'data'

Pretty sure you'll have to use AND/OR

HOWEVER, maybe this is a sign to change your data structure. Are you saying that there are 3 addresses, and any one of them could be 123 Main Street?

In that case, perhaps you need to pull address data out to a separate table.

In MySQL with MyISAM, you can create a FULLTEXT index

CREATE FULLTEXT INDEX fx_customeraddresses_123 ON FIN_LIVE.CUSTOMER_ADDRESSES (SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3)

and issue this query:

SELECT  *
FROM    FIN_LIVE.CUSTOMER_ADDRESSES
WHERE   MATCH(SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3) AGAINST ('+data')

, which will return all records with the word data in any of the fields.

You can even query it without the index:

SELECT  *
FROM    FIN_LIVE.CUSTOMER_ADDRESSES
WHERE   MATCH(SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3) AGAINST ('+data' IN BOOLEAN MODE)

, but this will be much slower.

If you are looking for exact match in any of three fields, you may use this syntax:

SELECT  *
FROM    FIN_LIVE.CUSTOMER_ADDRESSES
WHERE   'data' IN (SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3)

(works in all major databases).

No, you'll need to use AND/OR.

SELECT * 
FROM FIN_LIVE.CUSTOMER_ADDRESSES 
WHERE SYS_ADDRESS_1 = 'data'
    AND SYS_ADDRESS_2 = 'data'
    AND SYS_ADDRESS_3 = 'data'

If you want to avoid multiple AND statements to only enter the criterion, i.e. SYS_ADDRESS_1 = 'data', once, you could always do:

SELECT * 
  FROM FIN_LIVE.CUSTOMER_ADDRESSES 
 WHERE 
    SYS_ADDRESS_1 = SYS_ADDRESS_2 
    AND SYS_ADDRESS_2 = SYS_ADDRESS_3 
    AND SYS_ADDRESS_3 = 'data'

I'm only pondering why you would want to exclude something as elementary as an AND operator? I don't think I'm alone in that!

If you're looking for a row where any one of the columns match then you could do something like this, but it would be horribly inefficient compared to the simple OR statements.

SELECT
     *
FROM
     Customer_Addresses
WHERE
     '|*|' + sys_address_1 + '|*|' + sys_address_2 + '|*|' + sys_address_3 + '|*|' LIKE '%|*|' + @search_string + '|*|%'

I'll leave it as an exercise to the reader to turn this into an AND statement :)