CODEWITHSUNDEEP
mysqlsql
DevWithZachary
DevWithZachary

Reputation: 3685

Select in with join giving odd results

I have the below query which on the face seems to be working fine however its giving some strange results which im not sure are data issues or something wrong with the query itself.

The query is of the form:

SELECT j.username, u.email, u.accountType
FROM users u JOIN
     j_users j
     ON j.email = u.email
WHERE j.email IN (select email FROM j_users);

The users table may have many entries for the same email address, although the accountType will be different. However within the j_user table email is unique Whats strange here is the query is presenting results like:

|bob  |[email protected]  |admin|
|bob  |[email protected]  |user |
|     |[email protected]  |mod  |
|steve|[email protected]|admin|
|     |[email protected]|user |
|     |[email protected]|mod  |

Is there anything that can be seen from the query that could explain this? or a better way to extract the data I am interested in (all the values from users table who have an email in j_users)

EDIT

Thank you for pointing out the redundancy however my main issue still stands, some rows in the returned result set have no username, yet in the j_user table this is a required field and every row has one set. As already set each email is unique in this table as well so if the username is showing against one row surely it should show against all rows with the same email address?

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This query should do what you want:

SELECT j.username, u.email, u.accountType
FROM users u JOIN
     j_users j
     ON j.email = u.email;

Your query should also work, but it is redundant. You simply have missing user name from j_users.

Upvotes: 1

Related Questions