Geek asks and answers

SQL: compare if column values ​​in two tables are equal

I am working on mysql and have two tables with the same schema:

Pre-trial

|id|accusedId|articleid|
------------------------
|1 |     1   |     1   |
|2 |     1   |     2   |
|3 |     1   |     3   |
|4 |     2   |     1   |
|5 |     2   |     2   |

try

|id|accusedId|articleid|
------------------------
|1 |     1   |     1   |
|2 |     1   |     2   |
|3 |     2   |     1   |
|4 |     2   |     2   |

I want to get those accusedIdswhere everything articleIdsfor the first and second tables are equal.

The above example should only be returned by accused 2, the reason for the charge is 1, the second table does not contain article III,

I hope you understand what I mean. I am currently writing my dissertation in law, and the time when I was in sql is long gone. Of course, I have already done some research and tried several associations, but I could not find a solution. I hope you can help me.

+3
source share
1 answer

Try something like this:

select a.accusedId , sum(a.accusedid) as cnt_a, sum(coalesce(b.accusedId, 0)) as cnt_b
from a left join b on a.accusedId =  b.accusedId and a.articleId = b.articleId
group by accusedId
having cnt_a = cnt_b

, , . , , b, HAVING , .

+1

More articles:


All Articles