Torben Torben - 12 days ago 5
SQL Question

SQL - Determing if orders of a user with a certain email exists?

I have a blackout - I have the following table structure:

s_order:
userID | ....

s_user:
id | email | ...


I now loop through all my orders and can read the userID of the customer. I now want to check if there are other orders of this customer, not identified by his id but by his email address (because of migration, this can happen).

It can be, that in the s_user table I has the following data:

s_user:
id | email | ...
1 | hello@domain.com
2 | hello@domain.com


Can somebody help? Thanks!

Answer

Here is one method:

select u.*,
       (case when exists (select 1
                          from s_order o2 join
                               s_user u2
                               on o2.user_id = u2.id
                          where u2.email = u.email and u2.id <> u.id
                         )
              then 'Yes' else 'No'
        end) as HasDuplicate
from s_user u;

However, it might be sufficient just to see if multiple users have the same email. If so, this is the easiest way:

select u.email, group_concat(u.id) as userid
from s_user u
group by u.email
having count(*) > 1;

You could limit this just to users with orders:

select u.email, group_concat(u.id) as userid
from s_user u
where u.id in (select o.user_id from s_order)
group by u.email
having count(*) > 1;
Comments