mysql - Get 1 if number of rows in one table is equal to number of rows in other table -
i have following 2 tables
table a-> class_id | name | class 1 | abc | 1 | def | 2 | ghi | b 2 | jkl | b 2 | mno | b 3 | pqr | c 3 | stu | c table b-> class_id | class 1 | 1 | 2 | b 2 | b 3 | c 3 | c
i want result return 1 class_id = 1 & 3 , 0 class_id = 2, i.e. query should return 1 if number of rows in table equal no of rows in table b.
select class_id, acount = bcount count_matches (select class_id, count(*) acount tablea group class_id) join (select class_id, count(*) acount tableb group class_id) b using (class_id)
note query assumes there no missing class ids in tables. if 1 of them can missing class_ids, can use left or right join, depending on (and use ifnull(xcount, 0)
count table). if either of them can missing some, need full join, mysql doesn't support it. if search can find workaround solutions.
if want 1 class id @ time, it's simpler:
select (select count(*) tablea class_id = 1) = (select count(*) tableb class_id = 1) count_matches
Comments
Post a Comment