CODEWITHSUNDEEP
ms-accessin-clause
kb.
kb.

Reputation: 1060

MS Access IN operator not returning rows

Using an MS Access database (I know), the query below

SELECT * FROM PageImage WHERE (PageImage.Rooms) In ('1');

only returns rows when the 'PageImage.Rooms' column has a single value like 1

The actual data stored in the 'PageImage.Rooms' column is a comma separated list like 1,2,3 stored as a string, but no data is returned when this is the case.

From the above query & referring to the image below, only row 342 is returned, when I expect 341,342,343 to be returned

enter image description here

Upvotes: 0

Views: 815

Answers (3)

kb.
kb.

Reputation: 1060

I decided to scrap the multi-value string field & go down the correct route of creating a mappings table

Upvotes: 1

John Bollinger
John Bollinger

Reputation: 181169

Given that PageImage.Rooms stores a genuine comma-delimited list as you specified in a comment (i.e. it is not an Access 2007+ multi-value field), you really have created a bit of a mess. A table containing such a column is for that reason completely un-normalized (not even in first normal form). Query and data consistency issues are to be expected.

The usual way to model a many-to-many relationship such as you have would be with a separate table. In this case, however, since the PageImage entity seems to have no data other than its Id, you could do it in the same table. Either way, whichever table models the relationship should have a separate row for each (PageImage.Id, Room) pair, and those pairs should be a composite key for the table.

With the structure you have now, the query you seem to want could be implemented like this:

SELECT * FROM PageImage
WHERE
  PageImage.Rooms = '1'
  OR PageImage.Rooms LIKE '*,1'
  OR PageImage.Rooms LIKE '1,*'
  OR PageImage.Rooms LIKE '*,1,*'
;

As Gord Thompson observed, that could be expressed more compactly, and perhaps more efficiently, as

SELECT * FROM PageImage
WHERE (',' & PageImage.Rooms & ',') LIKE '*,1,*'
;

As you can tell, it gets worse if you want to compare against multiple room numbers in the same query.

Bottom line: strings containing comma-delimited lists are just strings as far as the database is concerned.

Upvotes: 2

Zaider
Zaider

Reputation: 2013

The IN operation is the same action as performing an expanded OR statement where SELECT * FROM PageImage WHERE (PageImage.Rooms) In ('1','2');

is the same as

SELECT * FROM PageImage WHERE PageImage.Rooms = '1' OR PageIMage.Rooms = '2';

Dealing with a partial match requires the LIKE keyword so SELECT * FROM PageImage WHERE PageImage.Rooms LIKE '*1*'; will return any field that has a one in any position within the field.

Upvotes: 0

Related Questions