What's the best way to join on the same table twice?
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:
- No need to specify INNER - it's implied by the fact that you don't specify LEFT or RIGHT
- Don't n-suffix your primary lookup table
- N-Suffix the table aliases that you will use multiple times to make it obvious
*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.
How to join same table twice on Access
In MS Access, more than one JOIN
requires parentheses pairings:
SELECT d.departmentID, d.depName, d.location, c1.memberID,
c1.fullName, c1.reportsTo, c2.fullName
FROM (Contacts as c1
INNER JOIN Departments as d
ON c1.departmentID = d.departmentID)
INNER JOIN Contacts as c2
ON c1.reprtsTo = c2.memberID
joining the same table twice on different columns
SELECT
complaint.complaint_text,
A.username,
B.username
FROM
complaint
LEFT JOIN user A ON A.user_id=complaint.opened_by
LEFT JOIN user B ON B.user_id=complaint.closed_by
MYSQL: Left join same table twice with same field
this is an example:
SELECT toD.dom_url AS ToURL,
fromD.dom_url AS FromUrl,
rvw.*
FROM reviews AS rvw
LEFT JOIN domain AS toD
ON toD.Dom_ID = rvw.rev_dom_for
LEFT JOIN domain AS fromD
ON fromD.Dom_ID = rvw.rev_dom_from
you can use Alias
Inner Join to Same Table Twice on same column
Sorry, I cannot comment. But I believe Peter is right in his comment. Since you are using 2 inner joins they both need to return results. Are you expecting both joins to find a match?
Try this and see which column is null. That is the join that is resulting in no returned rows.
SELECT
FAC.Vendedor
,REP.RepIDTabacal
,REP2.RepIDCtayOrden
FROM
ViewFacturacionDiaria_Test AS FAC
LEFT JOIN
ViewInformacionRepresentantes AS REP ON
REP.RepIDTabacal = FAC.Vendedor
LEFT JOIN
ViewInformacionRepresentantes AS REP2 ON
REP2.RepIDCtayOrden = FAC.Vendedor
WHERE
FecCpbte BETWEEN '2015-11-28' AND '2015-11-30'
Joining the same table twice in one query
A typical method uses left join
and coalesce()
:
select a.*,
coalesce(b.col1, c.col1) as col1
from a left join
b
on b.ext_id = a._id left join
c
on c.ext_id = a._id;
Use the coalesce()
for all columns that you want from the two tables.
MySQL: How do I join same table multiple times?
You need to use multiple LEFT JOINs
:
SELECT
ticket.ticket_id,
a1.attr_val AS attr_val1,
a2.attr_val AS attr_val2,
a3.attr_val AS attr_val3
FROM ticket
LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3
Here is an example: SQL Fiddle.
Related Topics
Humanized or Natural Number Sorting of Mixed Word-And-Number Strings
Get All Dates Between Two Dates in SQL Server
Can a Foreign Key Reference a Non-Unique Index
SQL Query to Get Aggregated Result in Comma Separators Along With Group by Column in SQL Server
Connect by Prior Equivalent For MySQL
Delete Duplicate Rows (Don't Delete All Duplicate)
Safely Rename Tables Using Serial Primary Key Columns
What's the Difference Between Varchar and Char
What MySQL Database Tables and Relationships Would Support a Q&A Survey With Conditional Questions
How to Use Distinct and Order by in Same Select Statement
Implement Paging (Skip/Take) Functionality With This Query
SQL Switch/Case in 'Where' Clause
SQL - If Exists Update Else Insert Into
Get the Week Start Date and Week End Date from Week Number
How to Query a Tree Structure Table in MySQL in a Single Query, to Any Depth