Daniel Daniel - 1 month ago 9
SQL Question

Get company name based on contact number or company number

I want to be able to look up the company name, based on a phone number.

I need an SQL query that allows me to look up a phone number that could be both a contact phone number, or a company phone number, and return the company name.

My table structure (simplified)

Contacts table



+----+--------+-----------+-----------+
| id | Name | Phone | CompanyID |
+----+--------+-----------+-----------+
| 1 | John | 258649864 | 3 |
| 2 | Martin | 258699182 | 2 |
| 3 | Amy | 296847348 | 2 |
+----+--------+-----------+-----------+


Company table



+----+----------------------+------------+
| id | Name | Phone |
+----+----------------------+------------+
| 1 | Virtual Company Inc. | 7070305030 |
| 2 | Worldwide 101 | 3050101020 |
| 3 | 24/7 Employee | 5023268293 |
+----+----------------------+------------+


I've tried something like this (when trying to look up the number 3050101020) but it does't work (return no rows).

SELECT Company.Name FROM Contacts
JOIN Company ON Company.id = Contacts.CompanyID
WHERE Contacts.Phone = '3050101020'
OR Company.Phone = '3050101020'

Answer

Do a FULL OUTER JOIN, in case there is a company without a contact, or a contact without a company specified:

SELECT DISTINCT Company.Name
FROM Company
FULL OUTER JOIN Contacts ON Company.id = Contacts.CompanyID
WHERE '3050101020' IN (Contacts.Phone, Company.Phone)

BTW, the original query seems to do just fine:

SQL>create table contacts (id int, name varchar(10), phone varchar(20), 
SQL&    companyid int);
SQL>insert into contacts values (1,'John','258649864',3);
SQL>insert into contacts values (2,'Martin','258699182',2);
SQL>insert into contacts values (3,'Amy','296847348',2);
SQL>create table company (id int, name varchar(30), phone varchar(20));
SQL>insert into company values (1,'Virtual Company Inc.','7070305030');
SQL>insert into company values (2,'Worldwide 101','3050101020');
SQL>insert into company values (3,'24/7 Employee','5023268293');
SQL>SELECT Company.Name FROM Contacts
SQL&JOIN Company ON Company.id = Contacts.CompanyID
SQL&WHERE Contacts.Phone = '3050101020'
SQL&OR Company.Phone = '3050101020';
name
==============================
Worldwide 101
Worldwide 101

                  2 rows found

You can throw in SELECT DISTINCT to get the company name only once. (You get it twice because the company has two contacts.)

And do a LEFT JOIN to get companies without contacts, or even a FULL OUTER JOIN to also get contacts without companies.