Data from Two Tables with Same Column Names

SELECT table1.name AS name1, table2.name AS name2, ...
  FROM table1
  INNER JOIN table2
    ON ...

with 
  p as (
    select 
      row_number() OVER (order by generation, parent) as rnum,
      generation,
      parent 
    from 
      table1
    order by
      generation,
      parent
  ), o as(
    select
      row_number() OVER (order by generation, parent) as rnum,
      generation,
      parent 
    from 
      table2 
    order by
      generation,
      parent
  )
select
  p.generation as table1_generation,
  p.parent as table1_parent,
  o.generation as table2_generation,
  o.parent as table2_parent
from
  p
left join o on 
  o.rnum+3=p.rnum
order by 1,2,3,4;

table1_generation	table1_parent	table2_generation	table2_parent
0	1	(null)	(null)
0	2	(null)	(null)
0	3	(null)	(null)
1	1	1	1
1	2	1	3
1	3	1	3
2	1	2	1
2	2	2	2
2	3	2	3

compare two tables having same column name but different date column names

This worked as well

with A as (
select distinct id1
from Table_A
where dt = '2022-04-10'
)

, B as (
select distinct id1
from  Table_B
where date = '2022-04-10')

select id1 from A
where id1 not in (select id1 from B)

select * from two tables with same column names mySQL

In my experience ORMs will run an initial DESCRIBE query so it can do this sort of stuff for you once it has the column names. But if you insist on doing it dynamically in a single query, you could do this with pure MySQL:

-- config
SET @database = 'your_database';
SET @tableA = 'table1';
SET @tableB = 'table2';

-- table alias "a" columns
SET @fieldsA = NULL;
SELECT GROUP_CONCAT(CONCAT('a.', COLUMN_NAME), ' AS ',CONCAT('`a.', COLUMN_NAME,'`')) INTO @fieldsA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @database AND TABLE_NAME = @tableA;

-- table alias "b" columns
SET @fieldsB = NULL;
SELECT GROUP_CONCAT(CONCAT('b.', COLUMN_NAME), ' AS ',CONCAT('`b.', COLUMN_NAME,'`')) INTO @fieldsB
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = @database AND TABLE_NAME = @tableB;

-- some variables for readability
SET @fields = CONCAT(' ', @fieldsA, ',', @fieldsB,' ');
SET @tableAliasA = CONCAT(' ',@database, '.', @tableA,' a ');
SET @tableAliasB = CONCAT(' ',@database, '.', @tableB,' b ');

-- generate our final query
SET @query = CONCAT('CREATE TABLE new_table SELECT', @fields,
                     'FROM', @tableAliasA,
                     'INNER JOIN', @tableAliasB,
                     'ON a.myID = b.myId WHERE a.age > 10 and b.ice = ''melted''');

-- finally run the query:
PREPARE stmt1 FROM @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- if you have problems with the above query, uncomment the following to get the query so you can run it separately
-- SELECT @query;

I'd strongly advise against using this sort of solution though. I'd sooner run an initial DESCRIBE query as earlier stated, then generate your query based on that. Another solution is to create a temporary table as a copy of the second table, then rename problematic columns, then proceed to join on it to produce the data you need to create your new_table. MySQL has no issues with result columns having the same name, the issue here is trying to create a table with two columns with the same name. So essentially what you're trying to do is a star select but excluding a column.

Another approach is to just select only the primary key from both:

SELECT a.myID as `aId`, b.myId as `bId` then create your table containing only that. Then if you ever need data from a particular table, just LEFT JOIN on it to grab the information you're looking for. You can take this a step further and set up a VIEW to do this sort of thing for you. VIEWs can join tables for you and make it very easy to select whatever columns you're looking for. You can also setup multiple VIEWs as well. Also note that views behave just like tables for the purpose of joins. You can JOIN a view with a table, or you can join a view with a view, etc.

So rather than do what you're trying to do -- creating a new table with the data from two other tables -- consider whether you're actually looking for a VIEW.

How to join two tables that are exactly the same column names with third table with unique column names to create new view?

Looking at the expected output, and the fact that table1 and table2 are structurally identical, you probably want UNION:

SELECT table1.foo, table1.bar, table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

UNION ALL

SELECT table2.foo, NULL,       table3.*
FROM      table1
LEFT JOIN table2 ON table1.lookup_column = table2.lookup_column
LEFT JOIN table3 ON table1.lookup_column = table3.lookup_column AND table3.date >= '2017-10-01'

How to Join two SELECT queries having same column names but different row values in both tables

UNION and UNION ALL are SQL operators used to concatenate 2 or more result sets. This allows us to write multiple SELECT statements, retrieve the desired results, then combine them together into a final, unified set.

The main difference between UNION and UNION ALL is that:

UNION: only keeps unique records

UNION ALL: keeps all records, including duplicates

UNION Example:

SELECT column1 AS datacheck from table1 
  UNION 
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
+-----------+

UNION ALL example:

SELECT column1 AS datacheck from table1 
  UNION ALL
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
|   data2   |
+-----------+

JOOQ join two tables with same column names

Metamodel is generated using jooq-codegen-maven plugin.
Interesting, I found out that using:

dsl.select(table.fields())...

solves problem. I would expect that this behavior is by default, but it is not.

---

Related Topics