SQL Join on Multiple Columns in Same Tables

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

SQL join on multiple columns in same tables?

You can implement it as a JOIN with two independent JOINs to CODE_TABLE, one for each value in DATA_TABLE:

SELECT A.COLUMN1,
       B1.CODE_NAME AS COLUMN2,
       B2.CODE_NAME AS COLUMN3
FROM DATA_TABLE A
JOIN CODE_TABLE B1 ON B1.CODE = A.COLUMN2
JOIN CODE_TABLE B2 ON B2.CODE = A.COLUMN3

If it's possible that a value from COLUMN2 or COLUMN3 in DATA_TABLE might not exist in CODE_TABLE, you should use LEFT JOIN instead, and COALESCE on the output so as not to return a NULL value:

SELECT A.COLUMN1,
       COALESCE(B1.CODE_NAME, '') AS COLUMN2,
       COALESCE(B2.CODE_NAME, '') AS COLUMN3
FROM DATA_TABLE A
LEFT JOIN CODE_TABLE B1 ON B1.CODE = A.COLUMN2
LEFT JOIN CODE_TABLE B2 ON B2.CODE = A.COLUMN3

Output (for your sample data)

COLUMN1 COLUMN2 COLUMN3
test    APPLE   LG
test1   SAMSUNG HWAWEI
test2   OPPO    GOOGLE

Demo on dbfiddle

Efficient way to join multiple columns to the same column? - SQL

You might find it easier to use a correlated subquery for each username, especially where you have to implement many columns as it's easier to cut n paste!

Something like:

select 
 (select Username from Mapping m where m.UserId = t.UserId1) Username1,
 (select Username from Mapping m where m.UserId = t.UserId2) Username2,
 (select Username from Mapping m where m.UserId = t.UserId3) Username3 etc
from InputTable t

SQL Join on multiple columns of the same table

Just add the other columns you want after SELECT, making a comma-separated list of which columns you want, in the order you want them to be returned:

SELECT P.id, municipalities.name, P.name
  FROM municipalities
  JOIN parishes as P
  ...

join same table with multiple columns and get the latest record with user data

I got the expected output with the below code.
I know there is some improvement in my code, but it is functional for now.

$user_id = $request->user_id;
$user_name = DB::table('users')->where('id', $user_id)->value('name');

$chats =DB::table('messages')
                    ->leftJoin('users as u1', 'messages.from_id', '=', 'u1.id')
                    ->leftJoin('users as u2', 'messages.to_id', '=', 'u2.id')
                    ->select('u1.id as user_id', 'u1.name', 'u2.name as name2', 'messages.id','messages.from_id', 
                    'messages.to_id', 'messages.body as message', 'messages.attachment', 'messages.created_at')
                    ->where('messages.from_id', $user_id)
                    ->orWhere('messages.to_id', $user_id)
                    ->latest()
                    ->get();

foreach($chats as $chat)
        {
            $chat->user_id = $chat->from_id == $user_id ? $chat->to_id : $chat->from_id;
            $chat->name = $chat->name == $user_name ? $chat->name2 : $chat->name;
        }
        $chats = $chats->unique('user_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

MySQL Select Left Join multiple columns from the same table

You will need to join two times to the table user, like this:

SELECT
    mpw.workflowUID,
    CONCAT(cu.user_firstName, " ", cu.user_lastName) AS "Workflow Created By",
    mpw.assginedTo,
    CONCAT(au.user_firstName, " ", au.user_lastName) AS "Workflow Assigned To"
FROM
    mainprojectworkflow AS mpw
INNER JOIN
    user AS cu ON cu.user_id = mpw.workflowUID
INNER JOIN
    user AS au ON au.user_id = mpw.assignedTo
WHERE
    projectID = $projectID
ORDER BY
    cid DESC

---

Related Topics