Comparing arrays in SQL

Question

I am creating a site for a card game and I have a problem with the search function.

I want to allow users to search for cards that match any number of options for a given field (e.g. the cards can be 1 of six different factions and I have a multi select box that allows users to select multiple factions to match). If the card can only be one of the options then I can simply build the query using the 'IN' sql operator and that works fine.

I run into a problem when the card has multiple subtypes in the database. Right now I am storing the subtypes as a comma separated list in a single field and I want to use the '&&' operator to compare the 2 sets but that is an array operator and the data in the table apparently isn't stored as an array so it isn't working.

Is there a way to do this?

General hint, never store data as CSVs, and things will become a huge amount easier for you — freefaller
– freefaller, Commented Aug 7, 2014 at 21:52
The IN operator is used to search for a row which has a column which has a value in a list you provide. It's not the other way around. So if you have a column with 100's of first names you can query: WHERE `firstName` in ('Adam', 'Robert'). So it's not like searching within a field. It's kind of short for: WHERE `firstName` = 'Adam' OR `firstName` = 'Robert' - although I don't know when either is more efficient or what the tipping point is when the one is more efficient than the other. — Bas van Ommen
– Bas van Ommen, Commented Aug 8, 2014 at 5:48
PS: I think you should elaborate on what you're trying to achieve. — Bas van Ommen
– Bas van Ommen, Commented Aug 8, 2014 at 6:12

Robert Harvey · Accepted Answer · 2014-08-07 21:53:00Z

5

Don't use comma-separated lists to store your card attributes; that's not going to work.

Either create new columns in your Cards table for each attribute, or join another table that contains the attributes in a one-to-many relationship with your Cards table.

answered Aug 7, 2014 at 21:53

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taryn Over a year ago

I'd recommend the second option -- join another table that contains the attributes in a one-to-many relationship with your Cards table -- don't use a separate column for each attribute that will become too difficult to maintain if you have a lot of attributes.

Adam Hollow Over a year ago

I can't use a separate column for each possible subtype because there are about 100 possible subtypes and each card is only going to have about 2 or 3 of them so that would be a horrible waste of space. Is there a way to store data in the dtatabase as an array?

Robert Harvey Over a year ago

Create another table that has a one-to-many relationship with your Cards table, and store the attributes (or subtypes, or whatever you're calling them) there, one record per Card-Attribute combination.

Collectives™ on Stack Overflow

Comparing arrays in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related