Join the Same Table Twice with Conditions

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 left join same table twice by different conditions to get correct group by result

Your query make a cartesian product, each account is join with other account

You need a single join :

SELECT
   p.id,
   COUNT(a.id) total_accounts,
   sum(case when a1.state = '0' then 0 else 1 end) inactive_accounts,
   sum(case when a1.state = '0' then 1 else 0 end) active_accounts
FROM Person p
  LEFT JOIN Account a
  ON a.person_id = p.id
WHERE p.id = 1
GROUP BY p.id;

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

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'

Getting duplicates when joining on the same table twice and another table once (3 total)

try adding this condition in where clause:

AND us.email = e.email

Culprit here is -> all emails are joined with all the users irrespective of their email id since all users and all emails have same plan_id. Hence the duplication.

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

Mysql IF conditional Left Join same table twice

In my opinion you should change your data model. As is, you cannot guarantee data consistency. You could put any number into task_inquirer_id and task_assigned_id; the DBMS cannot help you controlling that an agent ID really exists in the agent table for instance.

If the tables admin and agent are very similar as in your example, make it one person table, with a flag showing whether the person is an agent or an admin. You'd change the tasks table as follows:

task_id (PK)
task_inquirer_person_id (FK not nullable)
task_assigned_person_id (FK not nullable)

with foreign keys to the person table. Whether an admin or agent will be implicit by the records linked.

If the tables admin and agent differ very much, then only change your tasks table like this:

task_id (PK)
task_inquirer_admin_id (FK nullable)
task_inquirer_agent_id (FK nullable)
task_assigned_admin_id (FK nullable)
task_assigned_agent_id (FK nullable)

Add foreign keys to the main tables as shown and add constraints to ensure that always only one inquirer and one assigned person is set in a record.

Querying the table will be much simpler then.

Left join same table twice but one column is repeat

I think you're looking for:

SELECT B.NME AS Sports_BR, C.NME AS Leather_BR  
FROM BD_BRAND A
LEFT JOIN BD_REF B on B.ID =  A.Sports_BR and B.REF_TYPE = 'Sports_Brand' 
LEFT JOIN BD_REF C on C.ID =  A.Leather_BR and B.REF_TYPE = 'Leather_Brand' 

Your ID's are present multiple times due to the different reference types, so you need to specify which ID to grab by the reference type.

---

Related Topics