themistral Posted March 15, 2012 Share Posted March 15, 2012 Hi there, I have 2 tables. tbl_data id email firstname lastname 4 email@example.com Lizbeth Salander 5 email@example.com 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. Quote Link to comment 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 Quote Link to comment 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.... Quote Link to comment Share on other sites More sharing options...
mikosiko Posted March 15, 2012 Share Posted March 15, 2012 LEFT JOIN is better Quote Link to comment 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! Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 17, 2012 Share Posted March 17, 2012 Or you can use NOT EXISTS. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.