Pro Pro - 6 months ago 20
SQL Question

How to show the id that had not been assign on SQL?

Okay so I have two table here consist of issues and another table name assignee.

SELECT * FROM issue

id | key |
-
1 | ABC-1 |
2 | ABC-2 |
3 | ABC-3 |

SELECT * FROM assignee

a_id | issue_id | name
-
10 | 1 | John
20 | 2 | Freddy
30 | 2 | John


My first question is, how to show the issue keys for all issues that don't have an assignee on SQL for example the id 3 not being assigned to any?

My next question is, is a_id and issues_id are both primary key?

My last question is, how to improve this table for instance, Freddy and John are assigned to ABC-2. Should I create another table that only assign user_id and its name?

Answer

Question 1 - how to show the issue keys for all issues that don't have an assignee on SQL?

SELECT
 I.*
FROM Issue AS I
LEFT JOIN assignee AS A
ON I.id=A.issue_id
WHERE A.issue_id IS NULL

Question 2 - is a_id and issues_id are both primary key?

Ideally a_id should be primary key and issue_id should be foreign key referring to issue Table.

Question 3 -how to improve this table for instance, Freddy and John are assigned to ABC-2. Should I create another table that only assign user_id and its name?

It appears there is many to many relationship between issue and assignee. You can create another mapping table to maintain this relationship.