Select rows that end with the given parts in a join query?

Question

I'm trying to create a complex SQL query (at least for me) but really don't know where to start.

Basically, I have a character object, and each character can be made of several parts. For example:

character table:

id | character
--------------
1  | 你
2  | 是
3　　|　有

character_parts table:

id | character_id | part_id
---------------------------
1  | 1            | 4
2  | 1            | 9
3  | 1            | 5
4  | 2            | 2
5  | 2            | 34
6  | 2            | 43
7  | 3            | 21
8  | 3            | 16
9  | 3            | 41
10 | 3            | 43

So from that I know that:

Character 1 is made of parts 4, 9, 5
Character 2 is made of parts 2, 34, 43
Character 3 is made of parts 21, 16, 41, 43

Now what I would like to do is select all the characters that end by the specified parts.

For example, if I select all the characters that end by "16, 41, 43", I'll get Character 3. If I select all the characters that end by "43", I'll get Character 3 and 4.

I assume I need to build some query with subqueries, but not sure how to start, or if it can be done at all. So far I'm just selecting everything that include the required part IDs and then doing the comparison programmatically but this is too slow being I'm selecting way more than needed.

Any suggestion?

It seems to me there is one insufficient field in character_parts table. I would add a field for specifying order index of part. In other way how can you determine if (16, 41, 43) is the suffix? — heximal, Apr 29 '12 at 08:43
Is the order (21, 16, 41, 43) important? If so, you may need to add some kind of order column to the parts table. — Joachim Isaksson, Apr 29 '12 at 08:43
@heximal, in fact there is an ID column that I forgot to add (added now too). The order of the parts is important and they are sorted by ID. — laurent, Apr 29 '12 at 08:46

score 0 · Answer 1 · answered Apr 29 '12 at 08:48

the query might be like this.

select * from character where id in (select character_id from character_parts where character_id = 'required no' AND character_id = 'required no' AND character_id = 'required no')
//required number is the part_id you want to specify.

score 0 · Answer 2 · answered Apr 29 '12 at 08:59

Add another column, from_end that counts from the end, say:

from_end | id | character_id | part_id
--------------------------------------
      2  | 1  | 1            | 4
      1  | 2  | 1            | 9
      0  | 3  | 1            | 5
      2  | 4  | 2            | 2
      1  | 5  | 2            | 34
      0  | 6  | 2            | 43
      3  | 7  | 3            | 21
      2  | 8  | 3            | 16
      1  | 9  | 3            | 41
      0  | 10 | 3            | 43

Then you can do:

SELECT p0.character_id
  FROM character_parts AS p0
  JOIN character_parts AS p1 USING (character_id)
  JOIN character_parts AS p2 USING (character_id)
 WHERE p0.from_end = 0 AND p1.from_end = 1 AND p2.from_end = 2
   AND p0.part_id = 43 AND p1.part_id = 41 AND p2.part_id = 16

score 0 · Accepted Answer · edited May 23 '17 at 10:24

You could try group_concat function: http://www.sqlite.org/lang_aggfunc.html

SELECT group_concat(part_id) FROM character_parts WHERE character_id=1

The query should return 4,9,5.

The problem is that the order used by group_concat is arbitrary:

Sqlite group_concat ordering

So, assuming you have a field position that defines the order of the parts, we can update the query like this:

SELECT group_concat(part_id) FROM (SELECT part_id FROM character_parts WHERE character_id=1 ORDER BY position ASC)

The query will now return the 4,9,5 parts exactly the defined order.

Now that we have this value, we can search though it like a regular string. If we want to find all values ending with a certain string, we could use LIKE operator.

Finally the query would like like this:

SELECT character_id, parts_concat FROM (
    SELECT character_id, group_concat(part_id) FROM (
        SELECT character_id, part_id FROM character_parts WHERE ORDER BY position ASC
    ) GROUP BY character_id
) parts
WHERE parts_concat LIKE '%,9,5'

score 0 · Answer 4 · answered Apr 29 '12 at 09:17

SELECT  c.character
FROM    character_parts cp
JOIN    character c
ON      c.id = cp.character_id
WHERE   cp.part_id = 43
        AND cp.id =
        (
        SELECT  id
        FROM    character_parts cpo
        WHERE   cpo.character_id = cp.character_id
        ORDER BY
                id DESC
        )

Create the following indexes:

character_parts (part_id, character_id, id)
character_parts (character_id, id)

for this to work fast

Select rows that end with the given parts in a join query?

4 Answers4