How to Join Two Unrelated Tables in Sql

How to link two unrelated tables, through one table with common values?

This creates reproducible testing by cleaning up temp tables and re-inserting the data.
And could be a model for other solutions.

The SQL 'Select...' joins to a single TableC that has two parts to the ON condition-- prod_code and renewal_code (instead of two joins). Just remove the "a.*," to use it in your sql. (the decode function was changed to use a subscript of the first char of the A_Trans_Code).

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL DROP TABLE #TableA
GO
CREATE TABLE #TableA
    (     Reference          INTEGER 
        , Value_Name         VARCHAR(10)
        , Renewal_Code       VARCHAR(10)    )

    INSERT INTO #TableA VALUES( 1, 'A', 'N'   );
    INSERT INTO #TableA VALUES( 2, 'A', 'R'   );
    INSERT INTO #TableA VALUES( 3, 'B', 'N'   );
    INSERT INTO #TableA VALUES( 4, 'A', 'R'   );
    INSERT INTO #TableA VALUES( 4, 'A', 'N'   );

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB
GO
CREATE TABLE #TableB
    (     Reference          INTEGER 
        , Value_Name         VARCHAR(10)
        , Prod_Code          VARCHAR(10)    )

    INSERT INTO #TableB VALUES( 1, 'A', '0016'   );
    INSERT INTO #TableB VALUES( 2, 'A', '0027'   );
    INSERT INTO #TableB VALUES( 4, 'A', '0032'   );
    INSERT INTO #TableB VALUES( 4, 'A', '0032'   );

IF OBJECT_ID('tempdb..#TableC') IS NOT NULL DROP TABLE #TableC
GO
CREATE TABLE #TableC
    (     Prod_Code          VARCHAR(10)
        , A_Trans_Code        VARCHAR(10)
        , Commission_Percent INTEGER    )

    INSERT INTO #TableC VALUES( '0016', 'Renewal', 5   );
    INSERT INTO #TableC VALUES( '0027', 'Renewal', 5   );
    INSERT INTO #TableC VALUES( '0032', 'New',     10   );
    INSERT INTO #TableC VALUES( '0032', 'Renewal', 5   );

SELECT distinct a.*,  c.commission_percent 
          FROM #TableA a
          JOIN #TableB b ON a.reference = b.reference 
            AND a.value_name = b.value_name 
          JOIN #TableC c ON b.prod_code = c.prod_code 
           AND a.renewal_code = SUBSTRING(c.a_trans_code,1,1) 

Results are--

Reference   Value_Name  Renewal_Code    commission_percent
2           A           R               5
4           A           N               10
4           A           R               5 

Code to put in your sql

   ( SELECT distinct  c.commission_percent 
              FROM TableA a
              JOIN TableB b ON a.reference = b.reference 
                AND a.value_name = b.value_name 
              JOIN TableC c ON b.prod_code = c.prod_code 
               AND a.renewal_code = SUBSTRING(c.a_trans_code,1,1) 
    ) Commission_Percent

MySQL combine results of two unrelated tables

SELECT Z.Month, sum(Z.TotalFees) As 'Total Fees', sum(Z.TransportFees) As 'Transport Fees'
FROM
(
SELECT MONTHNAME(tblFeesPaid.Pay_Date) AS 'Month',
       SUM(tblFeesPaid.Fees_Paid) As 'TotalFees',
       0 As 'TransportFees'
FROM tblFeesPaid 
INNER JOIN tblFeesStructure ON tblFeesPaid.FID=tblFeesStructure.ID 
WHERE Year(tblFeesPaid.Pay_Date)=2022 
GROUP BY month(tblFeesPaid.Pay_Date)
UNION
SELECT MONTHNAME(tblTransFeesPaid.Pay_Date) AS 'Month',
       0 As 'TotalFees',
       SUM(tblTransFeesPaid.TransFee_Paid) As 'TransportFees' 
FROM tblTransFeesPaid 
        INNER JOIN tbltransfeesstructure ON tblTransFeesPaid.TransFID=tbltransfeesstructure.ID 
    WHERE Year(tblTransFeesPaid.Pay_Date)=2022 
    GROUP BY month(tblTransFeesPaid.Pay_Date)) Z
GROUP BY Z.Month;

How can I display data from two unrelated tables?

SELECT Alpha, Name, CONVERT(Cat_Id, CHAR(50)) AS Cat_Id, '' AS Dog_Id
FROM Cat
UNION ALL
SELECT Alpha, Name, '' AS Cat_Id, CONVERT(Dog_Id, CHAR(50)) AS Dog_Id
FROM Dog
ORDER BY Alpha

A few comments:

Strictly speaking, if we want to show empty string for missing cat or dog IDs, then we need to cast the Cat_Id or Dog_Id columns to CHAR, because all values in a column need to be the same type.

It is not necessary to wrap the UNION query to use ORDER BY and sort by the Alpha column, rather we can just add ORDER BY to the end.

How to join two unrelated table in MySQL?

select * from
(
select @rn:=@rn + 1 rn from t cross join (select @rn:=0) r
union 
select @rn:=@rn + 1 from t1
) allrows
left join 
(select col1,amount, @rn1:=@rn1 + 1 rn from t cross join (select @rn1:=0) r) t on t.rn = allrows.rn
left join
(select col2,amount, @rn2:=@rn2 + 1 rn from t1 cross join (select @rn2:=0) r) t1 on t1.rn = allrows.rn
where col1 is not null or col2 is not null;

where the all rows sub query works out the allocates a row number to the max possible number of rows which is then used later to join to the row numbers allocated to table1 and table2. NOTE there is nothing to order by so the results are not ordered..

Combine results of two unrelated queries into single view

SELECT t2.total_session,
       t1.watch_count
FROM
  (SELECT 1 AS common_key,
          count(*) AS watch_count
   FROM video
   WHERE monthname(views) = 'May') AS t1
JOIN
  (SELECT 1 AS common_key,
               sum(sessions) AS total_session
   FROM USER
   WHERE user_id = 6) AS t2 ON t1.common_key = t2.common_key;

Ofcourse, this will be very efficient only when the output in both t1 and t2 is one row.

---

Related Topics