CODEWITHSUNDEEP
mysqlsql
Nathan Q
Nathan Q

Reputation: 1902

Select rows where field in joining table not same value in every row

Is it possible in MySQL to see if a field in a join table does not have the same value in every row.

I tried to put it in an easy example in this sqlfiddle

-- Create tables
create table tbl_cake (
    id INT
  );

create table tbl_cake_piece (
    id INT,
    cake_id INT,
    share INT
  );

-- This cake is divided in 2 pieces with size 1/2
insert into tbl_cake values (1);
insert into tbl_cake_piece values (1, 1, 2);
insert into tbl_cake_piece values (2, 1, 2);

-- This cake is divided in 1 piece with size 1/2 and 2 pieces with size 1/4
insert into tbl_cake values (2);
insert into tbl_cake_piece values (3, 2, 2);
insert into tbl_cake_piece values (4, 2, 4);
insert into tbl_cake_piece values (5, 2, 4);

-- I want to select cakes that are not divided in equals pieces
-- So this query should return cake with id '2'

select * from tbl_cake c
join tbl_cake_piece p on p.cake_id = c.id

Upvotes: 2

Views: 338

Answers (1)

Aushin
Aushin

Reputation: 1208

Cakes that aren't cut in X equal pieces 

select cake_id
from tbl_cake_piece
group by cake_id
having count(distinct share) > 1

Upvotes: 4

Related Questions