• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

List functions: Comparing lists... in SQL statements?

Participant ,
Sep 21, 2008 Sep 21, 2008

Copy link to clipboard

Copied

Hello

I got a table with a column containing comma-separated values, p.e.
row 1: 45,67,2,90,67
row 2: 34,7,23,9,7
row 3: 4
row 4: 567,8,90

now I would like to find the rows containing p.e. 4 and 90. the search input comes in a list 4,90
my results should be row 1, row 3 and row 4.

how do I write the WHERE clause in SQL?

thank you so much for hints...
TOPICS
Advanced techniques

Views

366

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Participant , Sep 22, 2008 Sep 22, 2008
Dear Dan, dear Kronin555

I'm sorry about saying that I wasn't able to fix my data model. After sleeping a night over and dreaming about it, I changed the model as you guys suggested and it works fine.... I didn't understand right away what you meant!

Be sure I will show up again with many stupid questions :-)

So, many thanks for your inputs.

Votes

Translate

Translate
LEGEND ,
Sep 21, 2008 Sep 21, 2008

Copy link to clipboard

Copied

Normalize your database and you won't have problems like this. If you didn't understand the 1st 3 words, I have heard good things about the book, Database Design for Mere Mortals.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 21, 2008 Sep 21, 2008

Copy link to clipboard

Copied

You are right, I have no idea what 'Normalize your database' means! But actually my project is at a state where I don't touch the database design any longer. So unfortunately your input doesn't help much....

But thx anyway

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 21, 2008 Sep 21, 2008

Copy link to clipboard

Copied

As Dan alluded to, you need to fix your data model. However, you say you can't do this, but you ask: "how do I write the WHERE clause in SQL?"

SQL isn't designed to help you continue to use a faulty data model. Thus, there isn't a nice way to write what you want, and it's going to be dog-slow.

For each item in the list you're searching for (4,90 in your example), you need to check 4 options:
Is this element at the beginning of my list? WHERE row LIKE '#element#,%'
Is this element at the end of my list? WHERE row LIKE '%,#element#'
Is this element in the middle of my list? WHERE row LIKE '%,#element#,%'
Is this the only element in the list? WHERE row = '#element#'
You can see how this gets crazy.

<cfset listToSearchFor = "4,90">
<cfquery ...>
SELECT * FROM tablename WHERE
<cfloop list="#listToSearchFor#" index="idx">
row LIKE '#idx#,%' OR
row LIKE '%,#idx#' OR
row LIKE '%,#idx#,%' OR
row = '#idx#' OR
</cfloop>
1 = 2
</cfquery>

All of these OR LIKE checks are gonna make the query slower and slower.

In the future, never EVER store a comma-delimited list in a database field. You want to break it out into another table. How do you do that? I'm glad you asked... In your example, where Row 1 = "45,67,2,90,67", assume the primary key for that row is "12"...

Main_Table
---------------
id

Whatever_Table (You'd name this table based on what these ids stand for in the row list)
--------------------
main_id foreign key references main_table(id)
whatever_id

So your Main_Table would have an entry with an ID of 12 and Whatever_Table would contain:
main_id whatever_id
12 45
12 67
12 2
12 90
12 67

Now you can get all the whatever_id values for a given main_id by:
select whatever_id from whatever_table where main_id = 12;

And to answer your original question, you can do:
SELECT Main_Table.* FROM Main_Table JOIN Whatever_Table ON (Main.id = Whatever_Table.main_id)
WHERE whatever_id IN (4,90)

Lots easier, huh?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 22, 2008 Sep 22, 2008

Copy link to clipboard

Copied

LATEST
Dear Dan, dear Kronin555

I'm sorry about saying that I wasn't able to fix my data model. After sleeping a night over and dreaming about it, I changed the model as you guys suggested and it works fine.... I didn't understand right away what you meant!

Be sure I will show up again with many stupid questions :-)

So, many thanks for your inputs.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation