mysql - How to join two different tables horizontally (without cross join)

Question

I am working on a SQL project and i'm not good enough. I loaded data to a denormalized table. Then I inserted data to father and mother table from this denormalized table. Now I need to insert data to family table which has two foreign keys (mother_mother_id and father_father_id). So here is my thing, I want to take some columns from denormalized data like family_size, family_type and also for foreign keys, I need to take father_id from father table and mother_id from mother table.

According to other stackoverflow questions everyone suggested full outer join. But my tables have no common column to use an "on" situtation. For this issue I tried to join this data without any "on" condition. Then SQL apply cross join and I don't want this.

For example I've got these tables

Table A

carModel	year
x	1999
y	2005

question from:https://stackoverflow.com/questions/65869402/how-to-join-two-different-tables-horizontally-without-cross-join

Answer 1

I created a fiddle to solve this question:

https://www.db-fiddle.com/f/3MGCAaL1YU4ZdkRGfSwPPK/0

First the tables are created:

CREATE TABLE TableA (carModel varchar(20),year int);
INSERT INTO TableA VALUES('x',1999),('y',2005);

CREATE TABLE TableB (computer varchar(20),price int);
INSERT INTO TableB VALUES('t',1000),('z',2000);

CREATE TABLE TableC (food varchar(20),cals int);
INSERT INTO TableC VALUES('b',500),('y',600);

And then queried:

WITH A AS (
  SELECT ROW_NUMBER() OVER() R,
     carModel,year
  FROM TableA),
     B AS (
  SELECT ROW_NUMBER() OVER() R,
     computer,price
  FROM TableB),
     C AS (
  SELECT ROW_NUMBER() OVER() R,
     food,cals
  FROM TableC)
SELECT A.carModel, A.year, B.computer, B.price, C.food,C.cals
FROM A
INNER JOIN B ON B.R=A.R
INNER JOIN C ON C.R=A.R;