Compare a subquery with another subquery

Question

Compare a subquery with another subquery

Navigation

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

2

I have two tables, table A and table B, each with a column called text . Both tables have equal records up to a point, up to the character ' = '. What I want to do is SELECT all of the records in table A where the records up to the '=' character of it are the same as the records up to the '=' character of table B. I tried this here, but it only gives subquery problem only to be able to return a value.

SELECT texto FROM A
WHERE (SELECT LEFT(texto, POSITION("=" IN texto)) FROM A) = (SELECT LEFT(texto, POSITION("=" IN texto)) FROM B);

mysql sql

asked by anonymous 19.11.2017 / 18:13

2 answers

0

To get this result it is necessary to make a INNER JOIN :

SELECT A.'texto' FROM 'A'
INNER JOIN 'B' ON SUBSTR(A.'texto', 1, POSITION("=" IN A.'texto') - 1) = SUBSTR(B.'texto', 1, POSITION("=" IN B.'texto') - 1);

19.11.2017 / 18:35

Group items in table application asp.net web MVC Receiving notifications within an activity

score 0 · Accepted Answer

Instead of doing subquery, you can do INNER JOIN with GROUP BY :

SELECT A.texto FROM A
INNER JOIN B
ON
(LEFT(A.texto, POSITION("=" IN A.texto)) = LEFT(B.texto, POSITION("=" IN B.texto)))
GROUP BY A.texto

To use subquery, use IN like this:

SELECT texto FROM A
WHERE LEFT(texto, POSITION('=' IN texto))
IN
(SELECT LEFT(texto, POSITION('=' IN texto)) FROM B)