Jump to content

Group BY and NOT IN


themistral

Recommended Posts

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.

Link to comment
Share on other sites

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
Share on other sites

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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.