mysql - 在 MariaDB 中使用过滤器查询 1 到多合一记录

Question

我在 MariaDB 的架构中有这些表：

CLIENTI
id           | ragionesociale      | idZucchetti   | MORE COLS....
1            | SEA srl             | 569           | ....
2            | Contoso Srl         | 700           | ....
...

RIFERIMENTI_CLIENTI
ID  | riferimento         | idtipo        | idCliente  | MORE COLS....
1   | [e-mail]      | 2             | 1          | ....
2   | 003905623547        | 1             | 1          | ....
3   | 003905685748        | 6             | 1          | ....
4   | [e-mail]     | 2             | 2          | ....
5   | 0039562475          | 1             | 2          | ....
6   | 00398562586         | 6             | 2          | .... 
...

我现在做的基本 SELECT 语句是

SELECT SQL_CALC_FOUND_ROWS c.idzucchetti,
                           c.ragionesociale,
                           c.indirizzo,
                           c.citta,
                           c.cap,
                           c.prov,
                           (SELECT riferimento
                            FROM   riferimenti_clienti AS rc
                            WHERE  rc.idcliente = c.id
                                   AND rc.idanagrafica = -1
                                   AND idtipo = 1) AS tel,
                           (SELECT riferimento
                            FROM   riferimenti_clienti AS rc
                            WHERE  rc.idcliente = c.id
                                   AND rc.idanagrafica = -1
                                   AND idtipo = 6) AS fax,
                           (SELECT riferimento
                            FROM   riferimenti_clienti AS rc
                            WHERE  rc.idcliente = c.id
                                   AND rc.idanagrafica = -1
                                   AND idtipo = 2) AS email
FROM   clienti AS c

产生这个结果

QUERY RESULT
idZucchetti  | ragionesociale  ... | tel              | fax              | email
569          | SEA srl             | 003905623547     | 003905685748     | [e-mail]
700          | Contoso Srl         | 0039562475       | 00398562586      | [e-mail]

第一个问题：得到结果的正确方法是什么？

第二个问题：我需要过滤电话、传真或电子邮件列。当我写在“WHERE”子句中时

tel LIKE '%...%'

或传真或电子邮件，我收到此错误：

Error Code: 1054. Unknown column 'tel' in 'where clause'

我能怎么做？

标签： mysqlmariadbone-to-many