phpmyadmin Question posted in
The official documentation can be found here.

MySQL phpmyadmin inserting two values from two different tables and one given value into a third table

My problem is: I need to insert two values from two different tables and one given one by the exercise into a third table.

First, I figured I need the data from the two separate tables, therefore:

SELECT tablename1.id
FROM `tablename1`
WHERE tablename1.name LIKE "%example%";

SELECT tablename2.id
FROM `tablename2`
WHERE tablename2.name LIKE "%example%";

The third value is given by the exercise and it is a number

I came up with the following:

INSERT INTO tablename3 (tablename3.value1, tablename3.value2, tablename3.value3)
VALUES (SELECT tablename1.id FROM `tablename1` WHERE tablename.name LIKE "example", SELECT tablename2.id FROM `tablename2` WHERE tablename2.name LIKE "%example%"

plus the tablename3.value3 – which would be the number given by the exercise.

Another attempt of mine

INSERT INTO tablename3 (tablename3.value1, tablename3.value2, tablename3.value3)
VALUES (tablename3.value1 = (SELECT tablename1.id FROM `tablename1` WHERE tablename.name LIKE "example"), tablename3.value2 = (SELECT tablename2.id FROM `tablename2` WHERE tablename2.name LIKE "%example%"

then I need to add the tablename3.value3 – which would be the number given by the exercise.

tablename3.value 1 equals tablename1.id; tablename3.value2 equals tablename2.id; third value is the given number

I have tried it with several syntactic structures and with INSERT INTO SELECT; they all threw back an error message and I could not think of any other solution.

Can you please help me?
Thank you!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have overcomplicated the issue and needed to revise the logical connection between the tables.

    The solution for this:

    INSERT INTO tablename3 (tablename3,value1, tablename3.value2, tablename3.value3)
VALUES(
(SELECT tablename1.id FROM `tablename1` WHERE tablename1.name LIKE "example"),
(SELECT tablename2.id FROM `tablename2` WHERE tablename2.name LIKE "%example%"), number);

  2. 0

    You don’t specify what to do if there are multiple matches in the tables — or none at all. But I think you want:

    INSERT INTO tablename3 (value1, value2, value3)
    SELECT t1.id, t2.id, ?
    FROM table1 t1 CROSS JOIN
         table2 t2
    WHERE t1.name LIKE '%example%' AND
          t2.name LIKE '%example%';

    The ? is a parameter placeholder for the number provided for the rows being inserted.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search