Join Table Twice - on Two Different Columns of The Same Table

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

Join table twice - on two different columns of the same table

SELECT t2.table1_id
     , t2.id          AS table2_id
     , t2.table3_id_1
     , t2.table3_id_2
     , t31.value      AS x
     , t32.value      AS y
FROM   table2 t2
LEFT   JOIN table3 t31 ON t31.id = t2.table3_id_1
LEFT   JOIN table3 t32 ON t32.id = t2.table3_id_2;

There is no need to involve table1. table2 has all you need - assuming there is a foreign key constraint guaranteeing referential integrity (all t2.table1_id are actually present in table1). Else you may want to join to table1, thereby selecting only rows also present in table1.

I use LEFT [OUTER] JOIN (and not [INNER] JOIN) to join to both instances of table3 for a similar reason: it is unclear whether referential integrity is guaranteed - and whether any of the key columns can be NULL. An [INNER] JOIN would drop rows from the result where no match is found. I assume you would rather display such rows with a NULL value for any missing x or y.

table3.id needs to be UNIQUE, or we might multiply rows with several matches from each LEFT JOIN:

• Two SQL LEFT JOINS produce incorrect result

Why joining same table twice on two different columns returns different column values?

start with clean up syntax and put the same table on left

select distinct 
       usr.Name_FirstLast  AS AssignedTo 
     , usr1.Name_FirstLast as AssignedBy 
from tblNoteStore      nt_str
join tblNoteEntities   entit 
      ON entit.NoteGUID  = nt_str.ID  
join tblNoteDiaries  nt_dia 
      ON nt_dia.NoteGUID = nt_str.ID 
join tblNoteEntries    entri 
      on entri.NoteGUID = nt_str.ID 
     and entri.NoteGUID = entit.NoteGUID
     and entri.ID       = nt_dia.EntryGUID           
join tblNoteRecipients recip 
      ON recip.EntryGUID = entri.ID   
join tblUsers          usr 
      ON usr.UserGUID  = recip.UserGUID   -- returns AssignedTo column
join tblUsers          usr1 
      ON usr1.UserGuid = entri.UserGUID   -- returns AssignedBy column
where usr.UserGUID = '55610B2F-1997-40C0-9F01-EED3ED2939F9'  

this is silly on so many levels

tblUsers is the PK there is absolutely not reason to left to join to it

usr.UserGUID = '55610B2F-1997-40C0-9F01-EED3ED2939F9' was killing the left join

from
join tblNoteEntities entit
ON entit.NoteGUID = nt_str.ID

we know entit.NoteGUID = nt_str.ID
but you are repeating

 join tblNoteEntries entri 
      on entri.NoteGUID = nt_str.ID 
     and entri.NoteGUID = entit.NoteGUID  

the best sense I can make of this

select distinct 
       usr.Name_FirstLast  AS AssignedTo 
     , usr1.Name_FirstLast as AssignedBy 
from tblNoteStore      nt_str
join tblNoteEntities   entit 
      ON entit.NoteGUID  = nt_str.ID  
join tblNoteDiaries    nt_dia 
      ON nt_dia.NoteGUID = nt_str.ID 
join tblNoteEntries    entri 
      on entri.NoteGUID  = nt_str.ID 
     and entri.ID        = nt_dia.EntryGUID           
join tblNoteRecipients recip 
      ON recip.EntryGUID = entri.ID   
join tblUsers          usr 
      ON usr.UserGUID  = recip.UserGUID   -- returns AssignedTo column
     and usr.UserGUID = '55610B2F-1997-40C0-9F01-EED3ED2939F9'
join tblUsers          usr1 
      ON usr1.UserGuid = entri.UserGUID   -- returns AssignedBy column

Joining the same table twice with different values

Basically, you need another join on areas_group; to disambiguate the two references to the same table, you need to use a table alias.

Actually, it is a good practice to use table aliases for all tables that come into play in the query: this makes the query shorter to read and write.

SELECT 
    t.id AS transfer_id, 
    t.name AS transfer_name,  
    t.pickup_areas_group_id AS transfer_pickup_areas_group_id,
    ag1.area_id AS pickup_area_ids,
    t.drop_areas_group_id AS transfer_drop_areas_group_id, 
    ag2.area_id AS drop_area_ids
    tp.vehicle_id AS vehicle_id, 
    tp.date_start AS date_start, 
    tp.date_end AS date_end, 
    tp.price AS price 
FROM transfers t
INNER JOIN transfers_pricing tp ON tp.transfer_id = t.id
INNER JOIN areas_group ag1 ON ag1.id = t.pickup_areas_group_id
INNER JOIN areas_group ag2 ON ag2.id = t.drop_areas_group_id

JOIN the same table on two columns

I think you already have it done such that it can be considered good enough to just use as is :o)

Meantime, If for some reason you really-really want to avoid two joins on that country table - what you can do is to materialize below select statement into let's say `OEC.origin_destination_pairs` table

SELECT 
  o.id_3char o_id_3char,
  o.name o_name,
  d.id_3char d_id_3char,
  d.name d_name
FROM `OEC.country_names` o
CROSS JOIN `OEC.country_names` d   

Then you can just join on that new table as below

SELECT
  country_names.o_name AS origin,
  country_names.d_name AS dest,
  product_names.name AS product,
  SUM(data.export_val) AS export_val,
  SUM(data.import_val) AS import_val
FROM OEC.year_origin_destination_hs92_6 AS data
JOIN OEC.products_hs_92 AS product_names
   ON data.hs92 = product_names.hs92
JOIN OEC.origin_destination_pairs AS country_names
   ON data.origin = country_names.o_id_3char
  AND data.dest = country_names2.d_id_3char
WHERE data.year > 2012
  AND data.export_val > 1E8
GROUP BY
    origin,
    dest,
    product

The motivation behind above is cost of storing and querying in your particular case

Your `OEC.country_names` table is just about 10KB in size

Each time you query it you pay as if it is 10MB (Charges are rounded to the nearest MB, with a minimum 10 MB data processed per table referenced by the query, and with a minimum 10 MB data processed per query.)

So, if you will materialize above mentioned table - it will still be less than 10MB so no difference in querying charges

Similar situation with storing that table - no visible changes in charges

You can check more about pricing here

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.

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

---

Related Topics