Samuel Robert Samuel Robert - 1 month ago 9
MySQL Question

How to sort result based on the entry in other table

I've 2 tables in my schema

Table A

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | abc | NULL | abc |
| 2 | abc | NULL | NULL |
| 3 | NULL | abc | abc |
+----+------+------+------+


Table B

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| NULL | abc | abc |
| abc | NULL | abc |
| abc | abc | abc |
+------+------+------+


I want to select all the records in the Table A and sort them if it has same entry in the Table 2.

In this case my answer would be

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | abc | NULL | abc |
| 3 | NULL | abc | abc |
| 2 | abc | NULL | NULL |
+----+------+------+------+


Any help or notion would be appreciated

Answer

Something like this should work:

SELECT id, col1, col2, col3
FROM (
   SELECT id, col1, col2, col3, matches,
          @seq := IF(@id = id, @seq + 1,
                     IF(@id := id, 1, 1)) AS seq
   FROM (
      SELECT t1.id, t1.col1, t1.col2, t1.col3,
             IF(t2.col1 IS NULL, 0, 1) + IF(t2.col2 IS NULL, 0, 1) + 
             IF(t2.col3 IS NULL, 0, 1) AS matches
      FROM tableA AS t1
      LEFT JOIN tableB AS t2 
         ON COALESCE(t1.col1,0) = COALESCE(t2.col1,0) OR 
            COALESCE(t1.col2,0) = COALESCE(t2.col2,0) OR 
            COALESCE(t1.col3,0) = COALESCE(t2.col3,0) ) AS t
   CROSS JOIN (SELECT @seq := 0, @id :=0) AS vars         
   ORDER BY id, matches DESC  ) AS x
WHERE x.seq = 1 
ORDER BY x.matches DESC, x.id

The query uses a LEFT JOIN operation with OR operators in the ON clause, so as to get all possible matches of each record of tableA with each record of tableB. Variables are used in order to get the best matching record of tableA per id. The result is ordered by the number of matches of each tableA record.

Demo here

Note: The query assumes that col1, col2, col3 never take the value 0.