Select to compare value sets from columns of two tables

Question

Sirs,

First, I have 2 tables

Table 1:

ID    L1     L2     L3    L4     L5
------------------------------------
1     a      c      e     g      i
2     a      c      d     g      i
3     l      n      p     r      t

Table 2:

ID    L1    L2    L3    L4    L5    L6
----------------------------------------------
9     l     n     o     p     r     t          
8     a     c     d     e     g     i
7     a     b     c     d     e     f

I would like a select to search the data set of Table 1 (L1 to L5) within the data set of Table 2 (L1 to L6), so that the result is + or - like this:

Upshot:

ID    Count
-------------
9     1
8     2
7     0 

Since the ID 9 record in table 2 has 1 record of Table 1 that fits inside, so ID 8 has 2 and 7 has none.

I appreciate the help

Answer 1

SELECT
    T2.ID, 
    COUNT(T1.L1) AS 'Count'
FROM
    Tabela2 T2
LEFT JOIN
    Tabela1 T1 ON 
    (
        CONCAT('[', T2.L1 ,']','[', T2.L2 ,']','[', T2.L3 ,']','[', T2.L4 ,']','[', T2.L5 ,']','[', T2.L6 ,']')
        LIKE CONCAT('%[', T1.L1, ']%')

        AND 

        CONCAT('[', T2.L1 ,']','[', T2.L2 ,']','[', T2.L3 ,']','[', T2.L4 ,']','[', T2.L5 ,']','[', T2.L6 ,']')
        LIKE CONCAT('%[', T1.L2, ']%')

        AND

        CONCAT('[', T2.L1 ,']','[', T2.L2 ,']','[', T2.L3 ,']','[', T2.L4 ,']','[', T2.L5 ,']','[', T2.L6 ,']')
        LIKE CONCAT('%[', T1.L3, ']%')

        AND

        CONCAT('[', T2.L1 ,']','[', T2.L2 ,']','[', T2.L3 ,']','[', T2.L4 ,']','[', T2.L5 ,']','[', T2.L6 ,']')
        LIKE CONCAT('%[', T1.L4, ']%')

        AND

        CONCAT('[', T2.L1 ,']','[', T2.L2 ,']','[', T2.L3 ,']','[', T2.L4 ,']','[', T2.L5 ,']','[', T2.L6 ,']')
        LIKE CONCAT('%[', T1.L5, ']%')
    )
GROUP BY
    T2.ID

Test performed in Mysql. All records of Table 2 whose concatenation of columns L1 to L6 contains the values of columns L1 to L5 of Table 1 have been selected.

Ex.:

Porting to group 9 there is only one set that fits in it.

Note: I put each character between brackets to differentiate if the database contains [aaa] and [aa]. If it is not in square brackets when comparing aaa like '%aa%' would return true.