"IN" operator syntax

[cwalter]

I would like to determine whether the value of an expression is equal to any of the values of a list contained in a specified column field:

Suppose "fieldname" contains (value1, value2, ....)

What is the correct syntax for:

SELECT *
FROM Orders
WHERE ShipRegion In (fieldname)

Thank You very much.

 

[Pat Hartman]

Your table violates first normal form because you have a non-atomic attribute - ie a field that contains many values. You would have no problem searching if you normalize your table.

To search the non-atomic column, you will need to use the Like operator:

Where ShipRegion Like "*somevalue*";

You will need asterisks before and after the string you are searching for. If you use a partial string, you will get unexpected results. Fix your table structure. It will be easier in the long run.

 

[Gaufres]

How about if I want to select two records? In my case, this field contains "maybe", "yes", "no" and "NULL". I need to retrieve all records that has "maybe" and "yes" but some records has nothing.

SELECT distinct h.family_name, h.address, h.suburb, h.city, h.phone_home, h.phone_work, h.ge_long, h.ge_short, p.contact
FROM homestay h, [homestay people] p
WHERE h.homestay_id = p.homestay_id
AND h.city = 'Palmerston North'
AND h.ge_long Like "*maybe*";

I appreciate your help!
Gaufres

 

[Gaufres]

I figured it out just now..

SELECT distinct h.family_name, h.address, h.suburb, h.city, h.phone_home, h.phone_work, h.ge_long, h.ge_short, p.contact
FROM homestay h, [homestay people] p
WHERE h.homestay_id = p.homestay_id
AND h.city = 'Palmerston North'
AND h.ge_long In ('maybe','yes')
AND h.ge_short In ('maybe','yes');