How to search with several columns in one table until finding the value in another table ?, ORACLE

Navigation
0

Very good, someone knows how to perform a search between two tables, where the first has several columns and search with all those columns until finding a result in another table, I explain:

Table1 

| nombre | telefono1 | telefono2 | telefono3 |
|--------|-----------|-----------|-----------|
| usuri1 |    123    |    321    |   456     |

Table2 

| telefono | dato1 | dato2 | dato3 |
|----------|-------|-------|-------|
|   555    |   1   |   2   |   2   |
|   456    |   2   |   5   |   3   |

I need to search through all the telephone columns (telephone1, telephone2, telephone3) until I find a result in Table2 and return the result once found.

    
asked by Fabian 28.02.2018 в 16:48
source

2 answers

1
SELECT TELEFONO,DATO1,DATO2,DATO3 FROM (
            SELECT T2.*,ROW_NUMBER()OVER(PARTITION BY T2.TELEFONO ORDER BY NULL)DUPLICADOS FROM TABLA2 T2
            WHERE EXISTS (
                            SELECT * FROM TABLA1 T1                
                            WHERE 
                            (  
                               T2.TELEFONO = T1.TELEFONO1
                            OR T2.TELEFONO = T1.TELEFONO2
                            OR T2.TELEFONO = T1.TELEFONO3
                            )
                         )
         )WHERE DUPLICADOS=1;
    
answered by 05.03.2018 / 22:59
source
1

Well the truth, at this moment I can not think of many ways that do not have to go through the tables several times. There are probably better ways to do it, but this is an alternative: 

SELECT  t1.nombre,
        t2.telefono,
        t2.dato1,
        t2.dato2,
        t2.dato3
FROM Tabla1 t1
INNER JOIN Tabla2 t2
    ON t1.telefono1 = t2.telefono
UNION ALL
SELECT  t1.nombre,
        t2.telefono,
        t2.dato1,
        t2.dato2,
        t2.dato3
FROM Tabla1 t1
INNER JOIN Tabla2 t2
    ON t1.telefono2 = t2.telefono
    AND t1.telefono1 <> t2.telefono
UNION ALL
SELECT  t1.nombre,
        t2.telefono,
        t2.dato1,
        t2.dato2,
        t2.dato3
FROM Tabla1 t1
INNER JOIN Tabla2 t2
    ON t1.telefono3 = t2.telefono
    AND t1.telefono1 <> t2.telefono
    AND t1.telefono2 <> t2.telefono
;
    
answered by 28.02.2018 в 17:24
How to show only part of a string in sql server? RecyclerView duplicating items when starting the application