I have two tables.
Table 1 has id_a, id_b, id_t. Table 2 has id_t, name.
If Table 2 name starts with a, I need to find out of anything with matching id_ts also have matching id_as. If Table 2 name starts with b, I need to find out of any row with matching id_ts also have matching id_bs.
I need to know how many times these matches occur.
Table 1
id_a | id_b | id_t |
---|---|---|
1 | 0 | 123 |
1 | 0 | 123 |
2 | 0 | 123 |
0 | 4 | 456 |
0 | 4 | 456 |
0 | 5 | 456 |
0 | 5 | 456 |
0 | 5 | 456 |
0 | 6 | 456 |
0 | 7 | 456 |
Table 2
id_t | name |
---|---|
123 | aaq |
456 | bws |
So in this example, I want to see a result like
id_t | name | num_non_unique |
---|---|---|
123 | aaq | 1 |
456 | bws | 2 |
My current code is this:
SELECT
t2.id_t, t2.name, count(t1.*) AS num_non_unique
FROM
Table 2 AS t2
JOIN Table 1 as t1 ON t2.id_t = t1.id_t
WHERE
(t2.name like 'a%' and t1.id_a in (SELECT id_a FROM t1 GROUP BY id_a, id_t HAVING count(*) > 1))
OR (t2.name like 'b%' AND t1.id_b IN (SELECT id_b FROM t1 GROUP BY id_b, id_t HAVING count(*) > 1))
GROUP BY t1.name, t1.id_t
This doesn't currently give me the results I want. With this code I seem to get the count of all available rows for id_b, and 1 + non_uniques for id_a (so with one non-unique, the value is 2, otherwise the column has a 1).
Any help is appreciated!
Aucun commentaire:
Enregistrer un commentaire