How to Join Two Tables Together with Same Number of Rows by Their Order

SQL to combine columns for two tables with same number of rows

I found an example (possible solution) here and I extracted the solution for an easy reference:

CREATE TABLE #Temp_One (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

CREATE TABLE #Temp_Two (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

INSERT INTO #Temp_One
SELECT Your_Column FROM Your_Table_One ORDER BY Whatever

INSERT INTO #Temp_Two
SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever

SELECT * 
FROM #Temp_One a 
    LEFT OUTER JOIN #Temp_Two b 
         On a.RowNum = b.RowNum

In order to not repeat yourself writing it again and again, you could create a STORED PROCEDURE or a VIEW and call it each time you need from a 3rd party application. In this way, you make sure you respect the DRY concept:

  CREATE STORED PROCEDURE MyProc
    AS
    BEGIN
    --you could insert a TRY CATCH block -- not mandatory
        CREATE TABLE #Temp_One (
        [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
        [Description] [nvarchar] (50) NOT NULL
        )

        CREATE TABLE #Temp_Two (
        [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
        [Description] [nvarchar] (50) NOT NULL
        )

        INSERT INTO #Temp_One
        SELECT Your_Column FROM Your_Table_One ORDER BY Whatever

        INSERT INTO #Temp_Two
        SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever

        SELECT * 
        FROM #Temp_One a 
        LEFT OUTER JOIN #Temp_Two b 
             On a.RowNum = b.RowNum
    END
    GO

OR

CREATE VIEW MyView
AS

SELECT ....

GO

How to join two tables with the same number of rows in SQLite?

This is quite complicated in SQLite -- because you are allowing duplicates. But you can do it. Here is the idea:

• Summarize the table by the values.
• For each value, get the count and offset from the beginning of the values.
• Then use a join to associate the values and figure out the overlap.
• Finally use a recursive CTE to extract the values that you want.

The following code assumes that n and s are ordered -- as you specify in your question. However, it would work (with small modifications) if another column specified the ordering.

You will notice that I have included duplicates in the sample data:

WITH table_a (n) AS (
      SELECT 2 UNION ALL
      SELECT 4 UNION ALL
      SELECT 4 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5
     ),
     table_b (s) AS (
      SELECT 'valuex' UNION ALL
      SELECT 'valuey' UNION ALL
      SELECT 'valuey' UNION ALL
      SELECT 'valuez' UNION ALL
      SELECT 'valuez'
     ),
     a as (
      select a.n, count(*) as a_cnt,
             (select count(*) from table_a a2 where a2.n < a.n) as a_offset
      from table_a a
      group by a.n
     ),
     b as (
      select b.s, count(*) as  b_cnt,
             (select count(*) from table_b b2 where b2.s < b.s) as b_offset
      from table_b b
      group by b.s
     ),
     ab as (
      select a.*, b.*,
             max(a.a_offset, b.b_offset) as offset,
             min(a.a_offset + a.a_cnt, b.b_offset + b.b_cnt) - max(a.a_offset, b.b_offset) as cnt
      from a join
           b
           on a.a_offset + a.a_cnt - 1 >= b.b_offset and
              a.a_offset <= b.b_offset + b.b_cnt - 1
     ),
      cte as (
      select n, s, offset, cnt, 1 as ind
      from ab
      union all
      select n, s, offset, cnt, ind + 1
      from cte
      where ind < cnt
     )
select n, s
from cte
order by n, s;

Here is a DB Fiddle showing the results.

I should note that this would be much simpler in almost any other database, using window functions (or perhaps variables in MySQL).

How to merge two tables together with same number of rows corresponding to same id numbers?

It looks like you simply need to join your two tables and update table1

update t1
join t2 on t1.id = t2.id
set t1.code = t2.code

Join two tables where all the rows share the same values in join table

If you want lengths shared across all materials:

select ml.length
from material_length ml
group by ml.length
having count(distinct ml.material_id) = (select count(*) from material);

This aggregates by length and counts the number of distinct material ids. It then compares that number to the number of materials.

How can I join two tables with different number of rows in MySQL?

If you want all the results, you need an outer join, not an inner one. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")

Combine two tables which different number of rows in SQL server

Try using a full join to get all unique DisplayName, Category, NoOfLevels rows from both tables

select *
from (query1) t1 
full join (query2) t2 
    on t1.DisplayName = t2.DisplayName
    and t1.Category = t2.Category
    and t1.NoOfLevels = t2.NoOfLevels

Another possible solution is to use conditional aggregation without joins

select DisplayName, Category, NoOfLevels
, count(case when underoverestimate = 'Over' then Underoverestimate end) as OverCount
, count(case when underoverestimate = 'Under' then Underoverestimate end) as UnderCount
, Avg(case when underoverestimate = 'Over' then CaseDuration - EstDuration end) as ODA
, Avg(case when underoverestimate = 'Under' then CaseDuration - EstDuration end) as UDA
from DSU
where yearid between '2016' and '2018'
and underoverestimate IN ( 'Over' , 'Under' )
group by DisplayName, Category, nooflevels

---

Related Topics