Join two tables to produce a result set to loop through in order to fill in missing data in MySQL-CodePudding

I'm trying to join two tables with a query in order to loop through the result set to fill in missing data (labeled "NULL" in my example "result-table"). The database is internal and will only be accessed by me, so overhead isn't too much of a concern. My tables are structured this way:

table1

table1_id (PK, A_I)	record_name
1	name1
2	name2
3	name3

table2 (important_id and table1_id are indexed together)

table2_id (PK, A_I)	important_id	table1_id	data
1	1000	1	importantdata-a
2	1001	1	importantdata-b
3	1001	3	importantdata-c
4	1002	3	importantdata-d

How would I achieve a result like this:

result-table ("NULL" is what I need to fill in eventually by using a WHERE clause to filter them out)

table2_id (PK, A_I)	important_id	table1_id	record_name	data
1	1000	1	name1	importantdata-a
NULL	1000	2	name2	NULL
NULL	1000	3	name3	NULL
2	1001	1	name1	importantdata-b
NULL	1001	2	name2	NULL
3	1001	3	name3	importantdata-c
NULL	1002	1	name1	NULL
NULL	1002	2	name2	NULL
4	1002	3	name3	importantdata-d

I've reached my expertise limit in MySQL with the standard INNER/LEFT/RIGHT JOINs and even a CROSS JOIN, but nothing I've tried has given me the missing rows that I need to "fill in". I have an inkling that I may need a sub-query within a JOIN but my guesses do not get me close to what I'm looking for. Maybe I just need to create the missing rows (without the "importantdata") instead of trying to figure it out through a query?

CodePudding user response：

Start by generating important id for table1 cross joining to get all important_id/id combos then left join to table2

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T;
CREATE TABLE T(id INT,  record_name VARCHAR(20));
INSERT INTO T VALUES
(1  ,'name1'),
(2  ,'name2'),
(3  ,'name3');

CREATE TABLE T1 (id INT,    iid INT,    TID INT,VAL VARCHAR(20));
INSERT INTO T1 VALUES
(1  ,1000   ,1  ,'importantdata-a'),
(2  ,1001   ,1  ,'importantdata-b'),
(3  ,1001   ,3  ,'importantdata-c'),
(4  ,1002   ,3  ,'importantdata-d');

SELECT T1.ID,T.IID IMPORTANT_ID,T.ID TABLE1_ID,T.RECORD_NAME,T1.VAL DATA FROM
(
SELECT T.ID - 1   1000 IID ,T1.ID ID ,T1.RECORD_NAME FROM T CROSS JOIN T T1
)T
LEFT JOIN T1 ON T1.IID = T.IID AND T1.TID =T.ID
ORDER BY T.IID,T.ID;

See https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=f90596adc3e6cc8c294d4ba24fd2cc96

or if table2 contains all required values of important id

SELECT T1.ID,T.IID IMPORTANT_ID,T.ID TABLE1_ID,T.RECORD_NAME,T1.VAL DATA FROM
(
select t.id,t.record_name,iid from t cross join (select distinct iid from t1 ) t1
) T
LEFT JOIN T1 ON T1.IID = T.IID AND T1.TID =T.ID
ORDER BY T.IID,T.ID;