SQL query to get a single user based on username or email-CodePudding

I have a table Users with four columns like:

Users
- username
- email
- address
- name

Given a username and email, I want to find a single user in my table who has the same username and email, or the same username with the email not matching any other users, or the same email with the username not matching any other users.

For example, if this is my table:

username   email            address     name     id
jbrian     [email protected]     ""         John      1
gpaul                        ""         George    2
""         [email protected].  ""         George    3
""         [email protected].  ""         Tom       4

Searching for username jbrian and email [email protected] should return id 1.

Searching for username gpaul and email [email protected] should return nothing, because they are for different users.

Searching for username tking and email [email protected] should return id 4 because the email matches and there are no other matches for the username.

I put this together - select users.id from users where username='gpaul' or email='[email protected]' limit 1

But this would always return one result. I'm not sure how to enforce the constraint that only one result should be returned, or else nothing should be returned.

CodePudding user response：

This is achievable using like expression.

select id from users where email like concat(username, '%')

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/select.html Limit clause is used to limit the number of rows returned.

select users.id from users where username='gpaul' or email='[email protected]' limit 1

Therefore, the query cannot return more than 1 row.

OR: -> your query returns 1 row if either email or username not found, 2 if both are found (can be in different rows), and 0 if both username and email absent.

AND: -> your query returns 0 rows if email or username are not in the same row, and 1 if they are in the same row.

note: if there are repeating values of emails and names in the table, result can produce more rows. If that's the case you would want to make it unique to constrain the amount of rows you need as the output.

The query produces 0 rows if there is either email or username or the user_id's for email and username are different. And 1 row if its both.

select users.id from users where username='gpaul' limit 1
intersect
select users.id from users where email='[email protected]' limit 1

CodePudding user response：

For same username & email -> Pls change OR to AND

select users.id from users where username='gpaul' AND email='[email protected]'

If you want to check for both, same username & email AND same username with different email, try this out

SELECT id FROM users WHERE username = 'gpaul' AND id NOT IN(SELECT TOP 1 b.id FROM usersb WHERE username = 'gpaul' AND email = '[email protected]' ORDER BY id DESC)