1

First of all, many thanks to toph for solving my first issue query,

I currently have 2 tables, one for courses and one that links tutors to courses, originally I wanted to just get a list of all courses a certain tutor wasn't link to and toph solved this for me with the following code:

SELECT c.course_id, c.course
FROM course AS c
LEFT JOIN course_tutor_link AS ctl 
ON c.course_id=ctl.course_id AND ctl.users_id=$users_id
WHERE ctl.course_id IS NULL

which works like a charm my next step is to insert links for that tutor into course_tutor_link for the missing courses, I can do this with a bit of PHP looping through the results of above as a simple INSERT command resulting in

INSERT INTO course_tutor_link
(course_id, position, users_id)
VALUES(2,'swsa',9),(3,'swsa',9),(4,'swsa',9)... etc

But then I thought about whether or not I could do all of this as a single query, if it was just one set of values I could do it based on this SO link: MySQL: Insert record if not exists in table but I do not know how to extend it so that it cycles through each course_id value that is outputted from my original query

-----UPDATE----

It appears I did not give enough data at the start so my apologies to all and I hope this additional data will be suffice:

the table course has the following fields: course_id (PK), course, level, department_id

the table course_tutor_link as the following fields: ctl_id (PK), users_id, position, course_id

It is possible for a user to have different positions in different courses e.g be head of department for physics but be a tutor for chemistry. it is also possible that (at the start at least or when a new member joins) that a user will not be a member of any course and hence I cannot rely on just pulling the data for an existing position in the course_tutor_link as it may have the wrong position or the user not exist in it at all.

The values for users_id is held in a php variable called $users_id and the position is held in a php variable called $user_type

regards

Zen

Community
  • 1
  • 1
Zenonline
  • 102
  • 8
  • My answer is below but note that you're only selecting 2 values and inserting 3. I guessed at the select but you might want to modify.... ah $users_id. updating.. – Digital Chris Dec 30 '13 at 12:32
  • I actually only need to get the course_id (the course PK) from the course table as I already know the person position and users_id, I only had the c.course added in for my own info whilst doing the testing and printing out on phpAdmin – Zenonline Dec 30 '13 at 12:40

2 Answers2

1

Yes, you can do this using insert . . .select syntax:

INSERT INTO tutor_course_link(course_id, users_id)
    SELECT c.course_id, c.course
    FROM course c
    LEFT JOIN course_tutor_link ctl 
    ON c.course_id=ctl.course_id AND ctl.user_id=$users_id
    WHERE ctl.course_id IS NULL;

EDIT:

To add in position

INSERT INTO tutor_course_link(course_id, users_id)
    SELECT c.course_id, 'swsa', c.course
    FROM course c
    LEFT JOIN course_tutor_link ctl 
    ON c.course_id=ctl.course_id AND ctl.user_id=$users_id
    WHERE ctl.course_id IS NULL;
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • +1 You should also add `ctl.user_id` or `$users_id` to the SELECT list. – valex Dec 30 '13 at 12:33
  • Hi valex why? This may be me giving lack of information at the start: The select query is just pulling the course PKs out of the courses table that the user isn't currently attached to (in the course_tutor_link table) so that it can then be inserted into the course_tutor_table and avoid duplicates. – Zenonline Dec 30 '13 at 12:45
  • Also how do I add the position value into this?.. I'm getting the following error using this code #1136 - Column count doesn't match value count at row 1 – Zenonline Dec 30 '13 at 12:52
0
INSERT INTO tutor_course_link
(course_id, position, users_id)
VALUES (    
    SELECT c.course_id, 'swsa', $users_id
    FROM course AS c
    LEFT JOIN course_tutor_link AS ctl 
    ON c.course_id=ctl.course_id AND ctl.user_id=$users_id
    WHERE ctl.course_id IS NULL)
Digital Chris
  • 6,098
  • 1
  • 16
  • 28
  • I Think I get the idea here but there is a snag, the query you have given me implies that the INSERT course_id = c.course_id in the SELECT statement (true) but that position = c.course (false) and $users_id is stored in the course table (false its a php variable), The edited version should look like INSERT INTO course_tutor_link (course_id, position, users_id) VALUES ( (SELECT c.course_id FROM course AS c LEFT JOIN course_tutor_link AS ctl ON c.course_id=ctl.course_id AND ctl.users_id=9 WHERE ctl.course_id IS NULL),'swsa',9) but doesn't work for multiple rows – Zenonline Dec 30 '13 at 12:58
  • OK, If we are not using c.course, we shouldn't select it. Using $users_id is valid, it will be substituted with the value of $users_id by php before selecting it. But I'm not sure what to insert into position.... – Digital Chris Dec 30 '13 at 13:15
  • See edited solution. This will select the course_id, 'swsa', and the php variable $users_id and insert them. – Digital Chris Dec 30 '13 at 13:23
  • get a 1064 error :( I think the problem is that neither the position (in this case swsa) or users_id is actually stored in the course table, the course table only had the following fields course_id (PK), course, level and department_id, if the user is already affiliated with a course then it will be present in the course_tutor_link table but the users position can be different for each course and so cannot be relied upon to be plucked correctly from the SELECT statement – Zenonline Dec 30 '13 at 13:42
  • It helps to all all the relevant table structures, and the expected result at the start instead of discovering more and more about your data through comments. – Digital Chris Dec 30 '13 at 13:46
  • My apologies, i was trying not to put too much superfluous information in the originak question but it looks like I was too eager with the knife and cut too much out... I will update the original question with more data – Zenonline Dec 30 '13 at 13:56