MySQL Join and create new column value

Question

I have an instrument list and teachers instrument list.

I would like to get a full instrument list with id and name.

Then check the teachers_instrument table for their instruments and if a specific teacher has the instrument add NULL or 1 value in a new column.

I can then take this to loop over some instrument checkboxes in Codeigniter, it just seems to make more sense to pull the data as I need it from the DB but am struggling to write the query.

teaching_instrument_list

- id
- instrument_name

 teachers_instruments

- id
- teacher_id
- teacher_instrument_id

 SELECT
  a.instrument,
  a.id
 FROM
   teaching_instrument_list a
 LEFT JOIN
 (
    SELECT teachers_instruments.teacher_instrument_id
    FROM teachers_instruments
    WHERE teacher_id = 170
 ) b ON a.id = b.teacher_instrument_id

my query would look like this:

 instrument name    id   value
 ---------------    --   -----
 woodwinds          1    if the teacher has this instrument, set 1
 brass              2     0
 strings            3     1

What's the point of the surrogate key in teachers_instruments? — Kermit
– Kermit, Commented Feb 11, 2013 at 17:28
Sorry, but your question is a bit vague. Are you trying to count the number of teachers using each instrument in question? Then LEFT JOIN your ti table and use COUNT(teacher_id) to, well, do the counting. — raina77ow
– raina77ow, Commented Feb 11, 2013 at 17:37

raina77ow · Accepted Answer · 2013-02-11 18:03:31Z

2

One possible approach:

    SELECT i.instrument_name, COUNT(ti.teacher_id) AS used_by 
      FROM teaching_instrument_list AS i
 LEFT JOIN teachers_instruments AS ti
        ON ti.teacher_instrument_id = i.id
  GROUP BY ti.teacher_instrument_id
  ORDER BY i.id;

Here's SQL Fiddle (tables' naming is a bit different).

Explanation: with LEFT JOIN on instrument_id we'll get as many teacher_id values for each instrument as teachers using it are - or just a single NULL value, if none uses it. The next step is to use GROUP BY and COUNT() to, well, group the result set by instruments and count their users (excluding NULL-valued rows).

If what you want is to show all the instruments and some flag showing whether or now a teacher uses it, you need another LEFT JOIN:

    SELECT i.instrument_name, NOT ISNULL(teacher_id) AS in_use
      FROM teaching_instrument_list AS i
 LEFT JOIN teachers_instruments AS ti
        ON ti.teacher_instrument_id = i.id
       AND ti.teacher_id = :teacher_id;

Demo.

edited Feb 11, 2013 at 18:03

answered Feb 11, 2013 at 17:32

raina77ow

107k16 gold badges205 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2062046 Over a year ago

that's great! How can I add a where clause for only 1 user (WHERE ti.teacher_id = X)?

Muhammad Raheel · Accepted Answer · 2013-02-11 18:04:01Z

1

Well this can be achieved like this

SELECT
  id,
  instrument_name,
  if(ti.teacher_instrument_id IS NULL,0,1) as `Value`
from teaching_instrument_list as til
  LEFT JOIN teachers_instruments as ti
    on ti.teacher_instrument_id = til.id

Add a column and check for teacher_instrument_id. If found set Value to 1 else 0.

answered Feb 11, 2013 at 18:04

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

MySQL Join and create new column value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related