SQLite JOIN trouble-CodePudding

I'm trying to learn the basics of SQL by setting up a simple database in SQLite.

I created two tables, "people" and "departments"

The people table has three columns: ID(primary key), Name and Age;

The departments table has two columns: ID(primary key) and DeptName;

A single person can work in more than one department, so I created a third table "P2D" with two columns, "PpID" and "DpID" constrained to the ID columns of the other two tables. Maybe there's a better way to do this, but that's what I figured out (is it?)

Now, I need to make a query to display people name, age and departments. I made this:

SELECT Name 'Worker Name', 
Age, 
group_concat (DeptName,', ') Departments FROM People
LEFT JOIN P2D ON People.ID=P2D.PpID
LEFT JOIN Departments ON P2D.DpID=Departments.ID
GROUP BY Name;

And here's the output:

Worker Name         Age Departments
1 George Washington 30  
2 Peter             24  Storage, Accountancy
3 Roger             21  Sales, Storage
4 Wilco             71  Burps and Farts

As you can see, George Washington is not in any department, because I didn't put his ID into the P2D table. Is there a way to display something, like i.e. "Idle" instead of the blank NULL cell? Thanks, and sorry for my stuttering English.

CodePudding user response：

You could use COALESCE(), since GROUP_CONCAT() output is NULL for 0 results:

SELECT
  Name 'Worker Name',
  Age, 
  COALESCE(group_concat(DeptName, ', '), 'Idle') Departments
FROM People

COALESCE outputs the first non-null argument.

Personally, I rather do that bit in code, not in the db, because NULL is perfectly checkable in code/a template. You can print "Idle" instead of Departments, or show an icon, or remove a link, etc. If you always return text, your code doesn't know if it means department names, or actually no departments.

Your P2D solution is common and good. It's a link or pivot table.