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
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.