CODEWITHSUNDEEP
mysqlsql
John W
John W

Reputation: 33

INSERT JOIN WHERE issue

The design of the database is:

Members to Membership_Link (1 to Many).

Membership_Link to Membership_Type (Many to 1).

I am trying to insert data into Members while linking it to an item in Membership_Type. I am hoping to do this in 1 SQL command and not break it out into 3 queries.

INSERT INTO members
SET members.username = "Test"
    ,members.email = "[email protected]"
    ,members.password = "Password"
LEFT JOIN membership_link ON members.id = membership_link.user_id
RIGHT JOIN membership_type ON membership_link.type_id = membership_type.id
WHERE membership_type.name = "Admin"

Upvotes: 1

Views: 59

Answers (2)

Sakshi Garg
Sakshi Garg

Reputation: 559

After reading your question i hopeyou required something like creating a member, so i dont know why you required member_link and member_type

So, for this you just have to write

 INSERT into members values (name, email, passowrd);

But after reading your query written, seems like you required to update the member having member_type = admin

So, for this you have to do two queries

 SELECT members.id from members LEFT JOIN `membership_link` ON members.id=membership_link.user_id RIGHT JOIN `membership_type` ON membership_link.type_id=membership_type.id WHERE membership_type.name = "Admin"

After having the result you will get the id. So, update your row based of members.id

  UPDATE members SET members.username = "Test", members.email = "[email protected]", members.password = "Password" WHERE members.id = {id}

Updated Answer

You can do this thing with 3 queries there is no possible way of doing this as you have to insert data in two different tables

So you can first insert the data in members table and keep the id of it Then find the membership_type_id using selet query and then you can insert the value in the membership_link table

Upvotes: 1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

Use Update in place of Insert Because you are updating your Table not inserting.

Update members
LEFT JOIN `membership_link` ON members.id=membership_link.user_id
RIGHT JOIN `membership_type` ON membership_link.type_id=membership_type.id
SET members.username = "Test", 
members.email = "[email protected]", 
members.password = "Password"
WHERE membership_type.name = "Admin"

Upvotes: 1

Related Questions