I need help with a mysql query for php...
I have a table called 'table' with the following structure:
ID EMAIL GLOBAL_ID VENDOR_ID REVENUE USER
1 email1@email.com 123 7822 5 Bill
2 email1@email.com 123 90786 5 Paul
3 email1@email.com 123 17622 5 Mary
4 email2@email.com 124 1908 2 Eric
5 email3@email.com 125 189782 6 Jon
6 email3@email.com 125 11123 6 Paul
7 email4@email.com 126 11862 5 Dick
8 email4@email.com 126 271 5 Sue
9 email4@email.com 126 289871 5 George
10 email4@email.com 126 2211 5 Jack
11 email5@email.com 127 17771 10 Mary
12 email6@email.com 128 20891 5 Bill
13 email6@email.com 128 298981 5 Gary
I need a query to pull records where EMAIL and GLOBAL_ID together are unique (ie never include the same EMAIL/GLOBAL_ID combination twice) AND don't include if there are 3 or greater records that have the same EMAIL and GLOBAL_ID combination, AND i don't want to pull any records where USER = Jon. So, from the data above and using php to echo, the following results should be displayed:
EMAIL GLOBAL_ID
email2@email.com 124
email5@email.com 127
email6@email.com 128
*note i only need to echo out EMAIL and GLOBAL_ID
Any help with a query to echo this out in php to accomplish this would be much appreciated!
SELECT DISTINCT
EMAIL,GLOBAL_ID
FROM `table`
WHERE USER<>'Jon'
GROUP BY CONCAT(EMAIL,'@@',GLOBAL_ID)
HAVING COUNT(*)<3