user3368534 user3368534 - 5 months ago 12
SQL Question

select value from table that exist on another table condition by id value

I have two table tablea and tableb

tablea(id PK)

id value
__________
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j


tableb(id PK)

id value
__________
1 a
2 d
3 c
4 h
5 e
6 f
7 j
8 d
9 d
10 j


I want to compare these tables by id
i.e {1,3,4,5,6}
if id of tablea and tableb values are Same then i want Number of rows in return.

Answer
SELECT tablea.id
     , tablea.value AS valueA
     , tableb.value AS valueB
FROM tablea INNER JOIN tableb
  ON tablea.id = tableb.id
WHERE tablea.value = tableb.value
  AND tablea.id IN(1,3,4,5,6)

Then you can count the rows as well :

SELECT COUNT(*) AS commonRows
FROM tablea INNER JOIN tableb
  ON tablea.id = tableb.id
WHERE tablea.value = tableb.value
  AND tablea.id IN(1,3,4,5,6)