Query with inner join and LIKE operator

Question

Query with inner join and LIKE operator

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I have the following tables with their respective data

Table1

+----------+------------+
| IdTabla1 | Velocidad1 |
+----------+------------+
|     1    |   123.51   |
|     2    |   123.55   |
|     3    |   125.23   |
|     4    |   125.87   |
|     5    |   123.50   |
+----------+------------+

Table2

+----------+------------+--------+
| IdTabla2 | Velocidad2 | Estado |
+----------+------------+--------+
|    1     |   123.51   |    M   |
|    2     |   224.55   |    L   |
|    3     |  1245.55   |    I   |
|    4     |   125.87   |    U   |
|    5     |   123.58   |    M   |
+----------+------------+--------+

How can I get the speeds of both tables starting, for example, with: '123.5'?

SELECT ta.[Velocidad1], tb.[Estado]
FROM [dbo].[Tabla1] ta 
INNER JOIN 
    [dbo].[Tabla2] lb 
ON  ta.[Velocidad1] = tb.[Velocidad2] OR ta LIKE CONCAT('%123.5%');

sql sql-server query join select

asked by Devid 11.08.2018 в 10:05

source

3 answers

Pass value from a select to modal select How to use url segmentation in asp.net mvc?

score 1 · Answer 1

The idea is that you interpret it, my suggestion would be like that.

SELECT ta.IdTabla1 as Id,ta.Velocidad1,tb.Velocidad2, tb.Estado
FROM Tabla1 ta 
INNER JOIN Tabla2 tb 
ON  ta.IdTabla1 = tb.IdtTabla2 WHERE ta.Velocidad1 LIKE '%123.5%' OR tb.Velocidad2 LIKE '%123.5%'

score 1 · Answer 2

If you make sure that the 2 tables return the same number of columns with the same name, you can simply use Union ie you do the 2 queries and together the results:

SELECT IdTabla1 AS IdTabla,
      Velocidad1 AS Velocidad
FROM Tabla1 
WHERE Velocidad LIKE '125,5'

UNION

SELECT IdTabla2 AS IdTabla,
      Velocidad2 AS Velocidad
FROM Tabla2
WHERE Velocidad LIKE '125,5'

You can also enter the values that you want to search in a temporary table so that you do not have to write 2 times the values:

-- Creas una tabla temporal
DECLARE @valores TABLE (valor int);
-- introdcues los valores a buscar
INSERT INTO @valores VALUES(125.5), (80), (122);

SELECT IdTabla1 AS IdTabla,
       Velocidad1 AS Velocidad
FROM Tabla1 
WHERE Velocidad IN @valores

UNION

SELECT IdTabla2 AS IdTabla,
      Velocidad2 AS Velocidad
FROM Tabla2
WHERE Velocidad IN @valores

Or in case to make the union and use it as a subquery:

SELECT * FROM (
   SELECT IdTabla1 AS IdTabla,
      Velocidad1 AS Velocidad
   FROM Tabla1 

   UNION 

   SELECT IdTabla2 AS IdTabla,
      Velocidad2 AS Velocidad
   FROM Tabla2) AS unidos
WHERE velocidad LIKE '225.5'

score 0 · Answer 3

Friend, you can try the following query:

SELECT DISTINCT T1.IdTabla1, T1.Velocidad1, T2.IdTabla2, T2.Velocidad2, T2.Estado
FROM Tabla1 AS T1, Tabla2 AS T2
WHERE T1.Velocidad1 LIKE '%123.5%' 
AND T2.Velocidad2 LIKE '%123.5%'

What you notice about this query is that it shows you by pairs of results. Let's say to compare between the 2 tables where the condition is met and what ID, if it is not what you want you can use UNION and join the two SELECT as indicated in the previous answers.

SELECT T1.IdTabla1 AS ID, T1.Velocidad1 AS VELOCIDAD
FROM Tabla1 AS T1
WHERE T1.Velocidad1 LIKE '%123.5%'
UNION
SELECT T2.IdTabla2 AS ID, T2.Velocidad2 AS VELOCIDAD
FROM Tabla2 AS T2
WHERE T2.Velocidad2 LIKE '%123.5%'

I hope it works for you and you tell me.