Finding duplicates in multi-row SQLite-table

Question

I want to manage lists with a various number of entries. Currently I am using the following table structure.

CREATE TABLE lists (
    list_id INTEGER,
    sort_id INTEGER,
    content_id INTEGER,
    PRIMARY KEY (list_id,sort_id)
)

So I can call an individual list using:

SELECT content_id WHERE list_id=<<X>> ORDER BY sort_id

In principle adding a new list is not a problem, but how can I check for duplicates of a complete list before inserting a new set of a list? I give an example:

There is the following list already inserted:

list_id | sort_id | content_id
--------+---------+-----------
    3   |    1    |     7
    3   |    2    |     4
    3   |    3    |     9

A new list with the content 7,4,9 would be an duplicate and must not be inserted. But the content 4,7,9 or 7,4 or 7,4,9,5 must be insertet due to my definition of a different list-content.

I thought of adding a new table, that contains the concatenated content of a list, something like:

CREATE TABLE lists_concat (list_id PRIMARY KEY, content_concat UNIQUE)

So for my example there would be the entry

list_id | content_concat
--------+---------------
    3   |     7,4,9

This would work, but it seems to be very complicated. Is there an easier way? I am also open to use another table structure for my lists. Tell me please about your ideas.

Thanks a lot

CL. · Accepted Answer · 2016-07-04 09:31:10Z

1

Two list are equal if

the number of sort_ids is equal, and if
the values for all sort_ids match, i.e., there are no rows with matching sort_ids but different values.

Assuming the new list is stored in a separate table:

CREATE TEMPORARY TABLE new_list ( sort_id, content_id );

You can search for duplicates like this:

SELECT list_id
FROM (SELECT DISTINCT list_id
      FROM lists) AS L1
WHERE (SELECT COUNT(*)
       FROM lists as L2
       WHERE L2.list_id = L1.list_id
      ) =
      (SELECT COUNT(*)
       FROM new_list
      )
  AND NOT EXISTS (SELECT 1
                  FROM lists AS L3
                  JOIN new_list ON L3.list_id = L1.list_id
                               AND L3.sort_id = new_list.sort_id
                  WHERE L3.content_id != new_list.content_id);

edited Jul 4, 2016 at 9:31

answered Jul 1, 2016 at 15:09

CL.

182k18 gold badges241 silver badges282 bronze badges

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

1 Comment

Sven Richter Over a year ago

! ! ! The right parenthesis of the second to last line is wrong ! ! ! But thanks for the solution. I can use that!

Collectives™ on Stack Overflow

Finding duplicates in multi-row SQLite-table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related