MySQL multi-layer filtering based on criteria from one column -

in table i've got list of names, email addresses , column called referrer. users name , email entered multiple times table contest. referrer column tells me how contestant heard contest.

how recordset of contestants shows if referrer web and/or email choose 1 record each? example, contestant entered 10 times under email , 100 times under web. i'm trying single instance of each record.

this current query:

select * table_contestants  referrer in ('email', 'web') group email order referrer; 

of course, when use group by gives me 1 instance of record regardless of whether it's found have email or web.

removing group by give me instances, lot...

how write query give me 1 record of each contestant has both email , web?

there various solution depending on need:

if need distinct (name, email) pairs regardless of referer, use distinct keyword:

select distinct name, email tbl  referer in ("email", "web"); 

---

if need distinct (name, email, referrer) tuple, same solution applies:

select distinct name, email, referer tbl  referer in ("email", "web"); 

---

as extension sql, mysql allow in select clause reference non-aggregate column not referenced in group by clause. in case, value of non-aggregate column undetermined (understand: "is 1 of values of group" , "the result un-reproductible").

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

so if need distinct (name, email) pairs , one origin without preference, following query work with mysql:

select distinct name, email, referer tbl referer in ("email", "web") group name, email;  

---

see http://sqlfiddle.com/#!2/85292/5 comparison;