CODEWITHSUNDEEP
sqlpostgresql
MJ95
MJ95

Reputation: 479

Insert into a third table with many to many relationship

I have 3 tables, one with stock IDs, one with user IDs, and a third with stock IDs, user IDs, and user by stock IDs.

Table 1:

 | stockID 
 | 10001
 | 10002 
 | 10003 
 | 10004

Table 2:

|userID 
| 11000
| 11001
| 11002

Table 3:

|stockID |userID  |userByStock 
| 10001  | 11000    0001
| 10002  | 11000    0002
| 10003  | 11000    0003
| 10004  | 11000    0004
| 10001  | 11001    0005

In table 3, I would like to insert into and have that all users have all stock IDs, and that the userByStock always increments by one.

So basically, I need to grab all stock IDs that don't already exist in Table 3 and add them to each user and increment the userByStock column by 1.

I've tried something like: 

INSERT INTO table3 select table1.stockID FROM table1 WHERE table1.stockID NOT IN (SELECT stockID FROM table 3);

But I'm stuck

EDIT: I would also need to grab all userIDs from table2 that are not already in table3 and insert them into table3

Upvotes: 0

Views: 3148

Answers (4)

mlattari
mlattari

Reputation: 135

    create table table3 ( stockId smallint unsigned not null, userId smallint unsigned not null, userByStock smallint zerofill unsigned auto_increment primary key);

    insert into table3 (stockId, userId) select table1.stockId, table2.userId from table1 cross join table2;


    stockId | userId | userByStock |
    +---------+--------+-------------+
    |       1 |      1 |       00001 |
    |       2 |      1 |       00002 |
    |       1 |      2 |       00003 |
    |       2 |      2 |       00004 |
    |       1 |      3 |       00005 |
    |       2 |      3 |       00006 |
    +---------+--------+-------------+

But... If you can not add auto_increment...

   set @m:=(select max(userByStock) from table3); insert into table3 (stockId, userId, userByStock) select table1.stockId, table2.userId, (@m := @m + 1) from table1 cross join table2;

And... THE COMPLETE solution would be: 

   set @m:=(select max(userByStock) from table3); insert into table3 (stockId, userId, userByStock) select table1.stockId, table2.userId, (@m := @m + 1) from table1 cross join table2 where not exists(select * from table3 as t3 where t3.stockId = table1.stockId and t3.userId = table2.userId);

Upvotes: 0

Matt
Matt

Reputation: 14361

WITH cteAllPossibleCombinations AS (

    SELECT
       StockId
       ,UserId
    FROM
       Table1
       CROSS JOIN Table2
)

, cteMaxUserByStock AS (
    SELECT MAX(CAST(userByStock AS INT)) AS MaxUserByStock
    FROM
       Table3
)

INSERT INTO Table3 (StockId, UserId)
SELECT StockId, UserId, userByStock = m.MaxUserByStock + ROW_NUMBER() OVER (PARTITION BY 1)
FROM
    Table3 t
    LEFT JOIN cteAllPossibleCombinations x
    ON t.StockId = x.StockId
    AND t.UserId = x.UserId
    CROSS JOIN cteMaxUserByStock m
WHERE
    x.StockId IS NULL;

I guess the one note if you can I would modify table 3 and make it and actuall identity/auto increment column but if not just combine the max with a row_number and you should be good to go. You can also use the same technique with the WHERE () IN SELECT answer.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71424

I would consider simply doing an INSERT IGNORE assuming that you have a unique index across the stockID and userID fields on the join table.

INSERT IGNORE INTO table3 (stockID, userID)
SELECT table1.stockID, table2.userID
FROM table1 CROSS JOIN table2

This would try to insert the Cartesian (cross) join of table1 and table2 into table 3, ignore all rows where the unique index of stockID and userID already exist.

Assuming that your userByStock field is an autoincrement field, thee values inserted into that field would automatically increment.

This may be a good solution when you think the number of rows you need to add to table3 represent a signficant percentage of the rows combinations from table1 and table2. If you only have one or two rows to add to tables 3 and table3 has a signficant number of rows, this solution would probably not be as optimal as specifically identifying necessary inserts, as you would in essence be doing a bunch of inserts that are being ignored.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a select for both the table a where on coupled value in not in

    INSERT INTO table3  select table1.stockID, table2.userID 
    FROM table1, table2 
    WHERE (table1.stockID, table2.userID) NOT IN (SELECT stockID, userID FROM table 3);

Upvotes: 0

Related Questions