Relational MySQL Query

user_15181449 0
Viewed 51

I have a table called users:

+----+---------+--------+----------------+----------------+----------+
| ID |  Name   |  Zip   |     Email      |     Phone      | Username |
+----+---------+--------+----------------+----------------+----------+
|  0 | Jill    |  33333 | jill@aol.com   | (123)123-1245  | idjill   |
|  1 | Jack    | 11111  | jack@aol.com   | (123)111-1111  | idjack   |
|  2 | Bob     |  66666 | bob@aol.com    | (123)222-2222  | idbob    |
|  3 | jMarie  |  12345 | jill@aol.com   | (123)123-1245  | none     |
+----+---------+--------+----------------+----------------+----------+

If I run SELECT * FROM users WHERE Phone=(123)123-1245 will return both ID# 0 and 3.

What I would like to do is be able to select the user and but also return any other users that have the same phone or email but not zip code. So for example if I run SELECT * FROM users WHERE Username= idjill I'd like it to return user 0 and 3 because they both have the same phone number.

How can I do that? Thanks. If anyone has a better idea for a title to this post, please share. My first post, sorry.

Edit: I think I need to clarify my question a bit. So I have this select query right here:

SELECT *  FROM users WHERE Username = 'idjill' OR Email = 'idjill'

That perfectly returns ID 0, I would like it to return ID 0 and 3. Because the phone and the email match (I am using the same input to search between username and email).

How can I expand on this?

mysql
3 Answers

You can use nested query like this.

SELECT * 
FROM users 
WHERE Phone=(SELECT PHONE 
             FROM users 
             WHERE Name='Jill') OR 
      Email=(SELECT Email 
             FROM users 
             WHERE Name='Jill');
user_119566210

Nested query can be used

Select * 
from users
where Phone = (select Phone from users where Username = "idjill");
user_129651780

Using INNER JOIN like below.

SELECT DISTINCT a.* FROM users a INNER JOIN
 (SELECT * FROM users WHERE Username='idjill') b
 ON (a.Phone=b.Phone OR a.Email=b.Email) AND a.Zip<>b.Zip;
user_138410160
Related