SQL insert into table with values selected from other tables where an external value matches-CodePudding

I have the following tables:

Table1:

id	rarity
1	Common
2	Uncommon
3	Rare

Table2:

id	Type
1	Air
2	Earth
3	Fire
4	Water

The output table already exists and the schema is the following:

rarityID	weakness_typeID	resistance_typeID

and I should fill it with rows according to the Table2 and Table1.

For example if I'm given:

type is 'Water' and 'Air'
rarity is 'Common'

I'd like to add the IDs contained in Table1 and Table2 to this table to get the following updated output table:

rarityID	weakness_typeID	resistance_typeID
1	4	1

I've written the following query:

INSERT INTO table3 (rarityID, weakness_typeID, resistance_typeID)
    SELECT rar.id, weak.id, res.id
    FROM table1 rar, table2 weak, table2 res
    WHERE rar.rarity = `Common`
      AND weak.type = `Water`
      AND res.type = `Air`;

But it doesn't work, can you help me?

CodePudding user response：

My understanding of your problem is that you're trying to get ids for each of your information.

If this is correct, in order to do this you need to select their ids in three separate queries like it is done in the following query:

INSERT INTO table3 (rarityID, weakness_typeID, resistance_typeID)
SELECT (SELECT rar.id
        FROM   table1 rar
        WHERE  rar.rarity = 'Common') AS rarityID,
       (SELECT weak.id
        FROM   table2 weak
        WHERE  weak.type = 'Water') AS weakness_typeID,
       (SELECT weak.id
        FROM   table2 weak
        WHERE  weak.type = 'Air') AS resistance_typeID;

If you want to play with this code, check this SQL Fiddle.