CODEWITHSUNDEEP
sqlarrays
Adam Hollow
Adam Hollow

Reputation: 412

Comparing arrays in SQL

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?

Upvotes: 0

Views: 1424

Answers (1)

Robert Harvey
Robert Harvey

Reputation: 180808

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.

Upvotes: 5

Related Questions