SQL (MYSQL, Postgres) Lookup/report table

I’m basically making a lookup table for my three tables but its not doing what I want. Those 3 tables were created by loading 3 different csv files.

What I’m trying to do is inserting the ID’s from those tables into the lookup one.

This is what I keep getting:

---------------Lookuptable---------------
|lookup_ID|Table1_ID|Table2_ID|Table3_ID|
|    1    |    1     |        |         |   
|    2    |    2     |        |         | 
|    3    |    3     |        |         |
|         |          |    1   |         |
|         |          |    2   |         |
|         |          |    3   |         |
|         |          |        |    1    |
|         |          |        |    2    |
|         |          |        |    3    |

What I need is:

---------------Lookuptable---------------
|lookup_ID|Table1_ID|Table2_ID|Table3_ID |
|    1    |    1     |    1    |    1    |   
|    2    |    2     |    2    |    2    | 
|    3    |    3     |    3    |    3    |

I kind of get why this is happening, it inserts one row bellow every time with single inserts like

insert into Lookuptable(Table1_ID) select T1id from Table1;
and the others...

But I’ve tried nested ones too like

insert into Lookuptable(Table1_ID, Table2_ID, Table3_ID) 
select Table1.T1id, Table2.T2id, Table2.T2id from Table1, Table2, Table3;

but still doesn’t work. In fact this one just crashes the Mysql server and has an endless query on Postgres. I’ve tried other nested examples but none worked.

I’m also using Foreign Keys which work when I manually input a new value, but since the other tables come from loaded CSV files I have to input the values already there manually.

I’m really not sure what to do.

Answer

If I understand correctly what you want something like this should work

https://www.db-fiddle.com/f/k6CGsVXazSqJDfwKkdr6S7/1

SET @i:=0,@j:=0,@h:=0;

INSERT INTO Lookuptable
SELECT NULL,t1.ID,t2.ID,t3.ID FROM 
( SELECT @i:=(@i+1) AS temp_id,ID FROM Table1 ) t1
INNER JOIN
( SELECT @j:=(@j+1) AS temp_id ,ID FROM Table2 ) t2 ON t1.temp_id=t2.temp_id
INNER JOIN
( SELECT @h:=(@h+1) AS temp_id ,ID FROM Table3 ) t3 ON t2.temp_id=t3.temp_id;

Advertisement

Answer