CODEWITHSUNDEEP
mysqlsql
User7373
User7373

Reputation: 11

Select values from a join and replace null cells

I have to create a select query using the following relations:

I need to find the name of each team's captain, and if there isn't a captain I need to display 'No Captain'. The field captain p is the same data as member_id. Unfortunately, member_id is a primary key so I don't believe I can join on them. Boolean grad_p has a 0 if there is a captain and 1 if there isn't. If there is no team captain in the teams table, the team_captain tuples have data 'null'.

I have been working on this for a while and google searches have not helped me find a solution.

Upvotes: 1

Views: 47

Answers (1)

AdamMc331
AdamMc331

Reputation: 16691

You can use an outer join for this. An outer join will return all rows of one table, regardless of if it has any corresponding matches in the other table. In other words, if I outer join 'teams' and 'personnel' on the team_captain and member_id columns, I will get all teams even if the member is null. For example, let's say personnel looks like this:

| member_id | name |
+-----------+------+
|     1     | John |
|     2     | Jane |
|     3     | Jack |

And teams looks like this:

| team_id | team_captain |
+---------+--------------+
|    1    |       1      |
|    2    |       null   |
|    3    |       3      |

I can preform an outer join to get the team captain's name like this:

SELECT t.team_id, p.name
FROM teams t
LEFT JOIN personnel p ON p.member_id = t.team_captain;

And I will get the name, or a null field if none exists:

| team_id | name |
+---------+------+
|    1    | John |
|    2    | null |
|    3    | Jack |

Here is more information on outer joins. Now, a fancy function that MySQL offers is COALESCE. This is a function that returns the first non-null parameter. You can use it to replace those null fields with 'No Captain':

SELECT t.team_id, COALESCE(p.name, 'No Captain') AS captain
FROM teams t
LEFT JOIN personnel p ON p.member_id = t.team_captain;

Here is an SQL Fiddle example with my tables.

Upvotes: 3

Related Questions