Question about joins?

Question

SQL

Good afternoon.
There is a table containing contact information of users:
======================================== ==================
User_id | InfoType | Email | phone |
-------------------------------------------------- -------------
1 | 'Email' | '[email protected]' | NULL |
1 | 'phone' | NULL | '1234567'|
================================================= ==========
It is necessary to pull out all the contact information for a user with such a phone number using the phone number filter.

select * from users JOIN contact_info ON users.user_id=contact_info.user_id 
WHERE Phone  = '1234567'

in such a query, strings with email will not be included in the result.
How can it be solved? And is it possible to do with joins, without subqueries?

Answer 1

But there is no Phone field in the users table, it is in the contact_info table

SELECT * FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  WHERE UserPhone.Phone  = '1234567'

And if you needusers

SELECT users.*, contact_info.* FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  INNER JOIN users ON users.user_id = UserPhone.user_id
  WHERE UserPhone.Phone  = '1234567'

If you make a structure:

===========================
User_id | InfoType | Value |
---------------------------
1 | 'Email' | '[email protected]' |
1 | 'Phone' | '1234567' |
===========================

SELECT users.*, contact_info.* FROM contact_info 
  INNER JOIN contact_info AS UserPhone ON contact_info.user_id = UserPhone.user_id
  INNER JOIN users ON users.user_id = UserPhone.user_id
  WHERE UserPhone.InfoType = 'Phone' AND UserPhone.Phone  = '1234567'

Answer 2

You can try like this:

select pn.user_id, 
       MAX(pn.Phone), 
       MAX(ml.Email)
  from contact_info pn   
  JOIN users
    ON users.user_id = pn.user_id 
  LEFT
  JOIN contact_info ml
    ON ml.user_id    = pn.user_id 
   AND ml.InfoType      = 'Phone'   
 WHERE pn.user_id.Phone = '1234567'
   AND pn.InfoType      = 'Email'
 GROUP BY pn.user_id

But the storage structure is strange.
You have an InfoType field, why else would you have your own column for each InfoType?