CODEWITHSUNDEEP
mysql
asma
asma

Reputation: 57

MySQL error 1064 inner join

I write this SQL :

INSERT INTO registered (`idc`, `idp`, `date`)
SELECT 'idc' 
FROM course,registered
    INNER JOIN course c ON c.idc=registered.idc 
WHERE course.title='aaa',
      (SELECT `idp` FROM participant,registered
      INNER JOIN participant ON participant.idp=registered.idp WHERE 
      participant.username='mmm')

I get this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' (SELECT idp FROM participant,registered INNER JOIN participant ON pa' at line 4

Upvotes: 0

Views: 1728

Answers (3)

asma
asma

Reputation: 57

course idc int(11)PRI auto_increment title varchar(200) UNI details text participant idp int(11)PRI auto_increment firstname varchar(50) lastname varchar(50) username varchar(100)UNI registered idc int(11) unsigned PRI idp int(11) unsigned PRI date date

Upvotes: 1

McNets
McNets

Reputation: 10807

Your insert sentence expects 3 values: (idc,idp,date) but your select sentence only supply one: SELECT 'idc'

You should redefine your query as:

INSERT INTO registered (`idc`, `idp`, `date`)
SELECT course.idc, participant.idp, <HERE A DATE> 
FROM registered
    INNER JOIN course ON course.idc = registered.idc
    INNER JOIN participant ON participant.idp = registered.idp
WHERE 
    course.title = 'aaa'
    and participant.username = 'mmm';

But your INSERT sentence expects a date field as a 3rd field, you must supply it.

Upvotes: 0

Jeremy
Jeremy

Reputation: 1

You may want to try something like this where you set a parent SELECT with the two other SELECT statements after it...

INSERT INTO registered (idc, idp, date) 
  SELECT 
    (SELECT 'idc' FROM course, registered INNER JOIN course c ON c.idc=registered.idc WHERE course.title='aaa'), 
    (SELECT idp FROM participant,registered INNER JOIN participant ON participant.idp=registered.idp WHERE participant.username='mmm');

Also, you'll likely need to set "date" as well. If it's set automatically based on the definition for that column, you can leave it out: 

INSERT INTO registered (idc, idp)...

Upvotes: 0

Related Questions