3
\$\begingroup\$

I've two table (t1 and t2) with 3 identical integer columns: c1, c2 and c3. I want to count how many value in t1 are in t2.

SELECT count(t1.value) FROM t1 INNER JOIN t2 ON  (
    t1.c1 = t2.c1 OR t1.c1 = t2.c2 OR t1.c1 = t2.c3 OR
    t1.c2 = t2.c1 OR t1.c2 = t2.c2 OR t1.c2 = t2.c3 OR
    t1.c3 = t2.c1 OR t1.c3 = t2.c2 OR t1.c3 = t2.c3
)

It doesn't seems a good way to write it (I'll have to add some columns). Is there a better solution to write it without enumerated any possibilities?

I'm using MySQL version 5.6.

\$\endgroup\$
2
  • \$\begingroup\$ If t1 contains c1 = 1, c2 = 1, and c3 = 2 and the t2 has the values 1 and 2 somewhere, should that count as 1, 2, or 3 in the final total? \$\endgroup\$
    – rolfl
    Feb 25, 2014 at 15:46
  • \$\begingroup\$ I forget : t1.c1, t1.c2 and t1.c3 are all different. Idem for t2. So your example @rolfl should count 2. \$\endgroup\$ Feb 25, 2014 at 15:52

1 Answer 1

2
\$\begingroup\$

Your question is not very clear... but, the way I understand it is:

Collect all the unique values in t1, and count how many of those unique values appear in t2.

Interesting problem.... instead of a straight join with all the or conditions, which may lead to an internal cross-product (thousands of joins and results to run comparisons on), I would state the logic as a couple of subselects ... which represent the two sets of data... the unique values in t1, and the unique values in t2.

Note, the 'union' operator does a distinct as part of the union....

select count(*)
from
  (
      select c1 as val from t1
    union
      select c2 as val from t1
    union
      select c3 as val from t1
  ) as t1vals,
  (
      select c1 as val from t2
    union
      select c2 as val from t2
    union
      select c3 as val from t2
  ) as t2vals
where t1vals.val = t2vals.val

The code looks nicer this way, but requires scanning each table three times (which I think will be better than the potentially thousands of times it may have to happen with your query......

I have put together an sqlfiddle for this

\$\endgroup\$
1
  • \$\begingroup\$ It works fine, this is a good idea. It´s quite better than enumerate like I done, but it´s not factorized yet (maybe it´s not possible). Thanks. \$\endgroup\$ Feb 26, 2014 at 8:45

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .