This is a little complicated, but I have 2 tables. Let's say the structure is something like this:
*Table1* ID PhoneNumber1 PhoneNumber2 *Table2* PhoneNumber SomeOtherField
The tables can be joined based on Table1.PhoneNumber1 -> Table2.PhoneNumber, or Table1.PhoneNumber2 -> Table2.PhoneNumber.
Now, I want to get a resultset that contains PhoneNumber1, SomeOtherField that corresponds to PhoneNumber1, PhoneNumber2, and SomeOtherField that corresponds to PhoneNumber2.
I thought of 2 ways to do this - either by joining on the table twice, or by joining once with an OR in the ON clause.
SELECT t1.PhoneNumber1, t1.PhoneNumber2, t2.SomeOtherFieldForPhone1, t3.someOtherFieldForPhone2 FROM Table1 t1 INNER JOIN Table2 t2 ON t2.PhoneNumber = t1.PhoneNumber1 INNER JOIN Table2 t3 ON t3.PhoneNumber = t1.PhoneNumber2
This seems to work.
To somehow have a query that looks a bit like this -
SELECT ... FROM Table1 INNER JOIN Table2 ON Table1.PhoneNumber1 = Table2.PhoneNumber OR Table1.PhoneNumber2 = Table2.PhoneNumber
I haven't gotten this to work yet and I'm not sure if there's a way to do it.
What's the best way to accomplish this? Neither way seems simple or intuitive... Is there a more straightforward way to do this? How is this requirement generally implemented?
First, I would try and refactor these tables to get away from using phone numbers as natural keys. I am not a fan of natural keys and this is a great example why. Natural keys, especially things like phone numbers, can change and frequently so. Updating your database when that change happens will be a HUGE, error-prone headache. *
Method 1 as you describe it is your best bet though. It looks a bit terse due to the naming scheme and the short aliases but... aliasing is your friend when it comes to joining the same table multiple times or using subqueries etc.
I would just clean things up a bit:
SELECT t.PhoneNumber1, t.PhoneNumber2, t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2 FROM Table1 t JOIN Table2 t1 ON t1.PhoneNumber = t.PhoneNumber1 JOIN Table2 t2 ON t2.PhoneNumber = t.PhoneNumber2
What i did:
*One way DBAs avoid the headaches of updating natural keys is to not specify primary keys and foreign key constraints which further compounds the issues with poor db design. I've actually seen this more often than not.