Avatar of dwoolley3
dwoolley3
Flag for United States of America asked on

How do I write a SELECT query to find records having an item in a complex field that appears in another field

I would like to have a SELECT query (or statements) in SQL Server 2005 that selects records where one of the items in a Complex field (items separated by commas) matches another field in a view. When the Complex field contains only one item, it is trivial:

SELECT *
  FROM table
where Complex IN
(SELECT SMS
   FROM ComplexView)

Let's say that SMS could be potentially be some of these 4 values: A, B, ABC, BD
And let's say that Complex could contain any of those 4 values or a combination of them, separated by commas:
A
B
ABC
BD
A,B
A,ABC
A,BD
B,ABC
B,BD
ABC,BD
A,B,ABC
B,ABC,BD
A,B,ABC,BD

Thus, I want a record having Complex field of "A,BD" to be displayed if one of the SMS values is "A" or "BD"; however, the record would not display if SMS is only the value of "B".
Microsoft SQL Server 2005Microsoft SQL Server 2008ASP.NET

Avatar of undefined
Last Comment
dwoolley3

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
wdosanjos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Christopher Gordon

Does your base table (SELECT * FROM table) in your example have some kind of identity field or unique key?

If so, here is another approach.  It is dependent on a function called dbo.CSVToTable which I've attached.  The first recommendation may work fine, I just wanted to have some fun with this one :)


(see the EE Testing Code for more details after you've created the CSV To Table Function)

select      *
from      mytable

where      myUniqueId in
(

      select      distinct t1.myUniqueId

      from      myTable t1

      --parse the csv values in the base table to it's own table
      outer apply master.dbo.CSVToTable(t1.myInput, ',') matches

      --compare the parsed values from base table to acceptable values in complex.
      inner join MyComplexView complex on
            complex.sms = rtrim(ltrim(matches.code))
)

 CSVToTable.sql EE-Testing-Code.sql
Mlanda T

You need to first split the Complex column. Use the technique described here: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx and leverage the CROSS APPLY statemet. Then you can do:
select * from myTable CROSS APPLY [Split](Complex, ',') 
where Data IN (SELECT SMS FROM ComplexView)
order by myTable.Complex 

Open in new window

SOLUTION
dwoolley3

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anthony Perkins

>>Both queries, however, take a long time to go through 50,000 records.<<
Right, that is because it is a lousy design and you should seriously consider normalizing that data, if you expect any improvement in speed.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dwoolley3

ASKER
Since the query (that resolved my initial request) does take longer to execute than what we need, I have implemented a different design, which has given me good performance.

While I am satisfied with the solution of the first response and I have a good performing "new" design, I am open to suggestions on a design that normalizes the data. The customer record has an identity key and a field called Complex that has multiple SMS items in it, Would you suggest having the 20+ SMS fields in the customer record? though I was wanting to avoid having so many extra fields when most records will use just one SMS or two, but some records will use many of them. Alternatively, I could create a new table having pairs of the identity key (from the customer table) with each different SMS value (thus 1 to 20 or more pairs for each customer record).
Lowfatspread

create a table with 1 value per row ...

and query using EXISTS

select distinct Customerid
  from yourtable as A
 where exists (select customer from yourtable as x
                         where category='B'
                       and a.customerid=x.customerid)
 and exists (select customer from yourtable as x
                         where category='ABC'
                       and a.customerid=x.customerid)
 and exists (select customer from yourtable as x
                         where category='BD'
                       and a.customerid=x.customerid)



yourtable
customer  int not null,
category  char(10) not null,
primary key clustered  (customer,category)



 
Anthony Perkins

>>Alternatively, I could create a new table having pairs of the identity key (from the customer table) with each different SMS value (thus 1 to 20 or more pairs for each customer record). <<
That is the way to go.  And make sure you create a Primary Key on those two columns.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dwoolley3

ASKER
Okay. I am open to the possibility of having an additional table as mentioned by Lowfatspread and acperkins. I also appreciate the details provided by Lowfatspread.

Perhaps we can integrate the query idea of Lowfatspread with my initial query.

SELECT *
  FROM table
where Complex IN
(SELECT SMS
   FROM ComplexView)

The bottom part, (SELECT SMS FROM ComplexView) is a subset (maybe 5 or 10) of the 20+ valid SMS values. I'd like to select all fields of all records from my "table" where the customer's record has an SMS (or category according to the table made by Lowfatspread) that appears within the subset. Can someone help me finish this query and replace the "where Complex IN" with something that would do the job?
dwoolley3

ASKER
Since my original question was answered, I awarded points accordingly, yet I appreciated the alternative suggestions of creating another table to normalize the data. In the end, I created a totally different way to achieve my desired results.