themistral Posted March 15, 2012 Share Posted March 15, 2012 Hi there, I have 2 tables. tbl_data id email firstname lastname 4 [email protected] Lizbeth Salander 5 [email protected] Lizbeth Salander The second table just contains a record id. This record id relates to the record id in tbl_data tbl_processed id 4 I run a query to get the ids from tbl_processed and store them in an array So, the raw query I am running is SELECT * FROM tbl_data WHERE id NOT IN ('4') GROUP BY email, lastname, firstname; If I run this, id 5 is still returned. The behaviour I want to see is that if there is a matching ID in the grouping, no result is returned. I am not sure I am using the correct logic, and I hope this makes sense! Basically, I want to group my data, then if there are any matches in the NOT IN statement within that grouping, I don't want to return a result for that group. Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/ Share on other sites More sharing options...
mikosiko Posted March 15, 2012 Share Posted March 15, 2012 will be better to use a LEFT JOIN between both tables and filter when tbl_processed.id is null Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1327716 Share on other sites More sharing options...
Muddy_Funster Posted March 15, 2012 Share Posted March 15, 2012 OK, I have no idea why you are using group by, there's no good reason for it with the data you have presented, but I expect you're trying something like: SELECT email, firstname, lastname FROM tbl_data WHERE tbl_data.id NOT IN (SELECT id from tbl_processed) I tested the code you posted and it does not include the row with an id of 4 when I do it.... Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1327717 Share on other sites More sharing options...
mikosiko Posted March 15, 2012 Share Posted March 15, 2012 LEFT JOIN is better Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1327722 Share on other sites More sharing options...
themistral Posted March 16, 2012 Author Share Posted March 16, 2012 OK I think I need to explain this a bit better. My data can contain the same details multiple times over. If an ID exists in tbl_processed that references one instance of a user, I don't want that user to show up again in my results. So, in my basic example, if ID 4 exists in tbl_processed, I don't want IDs 4 or 5 to be returned by my query because they are the same data. I hope that makes it a little clearer. Thanks! Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1328018 Share on other sites More sharing options...
Muddy_Funster Posted March 16, 2012 Share Posted March 16, 2012 Then you really should be fixing your tables, not working around the problem of duplicate data being in where it shouldn't be. Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1328034 Share on other sites More sharing options...
fenway Posted March 17, 2012 Share Posted March 17, 2012 Or you can use NOT EXISTS. Link to comment https://forums.phpfreaks.com/topic/258996-group-by-and-not-in/#findComment-1328420 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.