Select that compare sets of column values from two tables

Question

Select that compare sets of column values from two tables

Navigation

#1 by (2 votes)

4

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 look up the data set from Table 1 (L1 through L5) within the data set from Table 2 (L1 to L6), so that the result would be + or - thus:

Result:

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

Since the ID 9 record in Table 2 has 1 record of Table 1 that fits in, just as ID 8 has 2 and 7 has none.

Thanks for the help

mysql sql

asked by anonymous 27.09.2016 / 21:50

1 answer

How to change the form's buttons (minimize, maximize and close) Doubt with scenes (Scene)

score 2 · Accepted Answer

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 the records in Table 2 were selected whose the concatenation of columns L1 to L6 contains the values of columns L1 to L5 of Table1.

Eg:

Movingtogroup9thereisonlyonesetthatfitsin.

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