how to find number of records in both table using join. i have two tables table1 and table2 with same structure.
table1
| id | item |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | A |
| 2 | B |
table2
| id | item |
|---|---|
| 1 | A |
| 1 | B |
| 2 | A |
| 2 | B |
| 2 | C |
| 2 | D |
Output should be like this.
| id | table1.itemcount | table2.itemcount |
|---|---|---|
| 1 | 3 | 2 |
| 2 | 2 | 4 |
SELECT DISTINCT id, (
SELECT COUNT(*) FROM table1 AS table1_2 WHERE table1_2.id=table1.id
) AS "table1.itemcount", (
SELECT COUNT(*) FROM table2 AS table2_2 WHERE table2_2.id=table1.id
) AS "table2.itemcount"
FROM table1;
Assuming that each id is guaranteed to exist in both tables, the following would work
select
t1.id,
count(distinct t1.item) t1count,
count(distinct t2.item) t2count
from t1
join t2 on t1.id = t2.id
group by 1;
But if that is not guaranteed then we'll have to use full outer join to get unique ids from both tables
select
coalesce(t1.id, t2.id) id,
count(distinct t1.item) t1count,
count(distinct t2.item) t2count
from t1
full outer join t2 on t1.id = t2.id
group by 1;
We're using coalesce here as well for id because if it only exists in t2, t1.id would result in null.
@DeeStark's answer also works if ids are guaranteed to be in both tables but it's quite inefficient because count is essentially run twice for every distinct id in the table. Here's the fiddle where you can test out different approaches. I've prefixed each query with explain which shows the cost
Hope this helps