Fiddle: http://sqlfiddle.com/#!9/4d4288/6
DDL and DML:
CREATE TABLE User(
id INT,
Name VARCHAR(50)
);
INSERT INTO User (id, Name)
VALUES
(1, 'Name 1'),
(2, 'Name 2'),
(3, 'Name 3');
CREATE TABLE Log(
Id INT,
Description VARCHAR(255));
INSERT INTO Log (id, Description)
VALUES
(1, 'This is issue 1 ...'),
(2, 'This is issue 2 ...'),
(3, 'This is issue 3 ...'),
(4, 'This is issue 3 ...');
CREATE TABLE LogActions(
id INT,
LogID INT,
UserID INT,
Status ENUM('Approved', 'Refused'));
INSERT INTO LogActions (id, LogID, UserID, Status)
VALUES
(1,1,1,'Approved'),
(2,1,2,'Approved'),
(3,2,1,'Approved'),
(4,2,3,'Approved');
Output
SELECT
l.id,
l.description,
u.name,
la.status
FROM Log l
LEFT JOIN
LogActions la ON l.id = la.LogID
LEFT JOIN
User u ON u.id = la.UserID
---------------------------------------------
id description name status
1 This is issue 1 ... Name 1 Approved
1 This is issue 1 ... Name 2 Approved
2 This is issue 2 ... Name 1 Approved
2 This is issue 2 ... Name 3 Approved
3 This is issue 3 ... (null) (null)
4 This is issue 4 ... (null) (null)
Summary
Here I can see which user marked specific log number and also which records havent.
Within the .NET application, I ensure the records are marked by 2 different users and no more than 2 records are checked off.
Goal
This is my desired output.
id description name1 name2 status1 status2
1 This is issue 1 ... Name 1 Name 2 Approved Approved
2 This is issue 2 ... Name 1 Name 3 Approved Approved
3 This is issue 3 ... (null) (null) (null) (null)
4 This is issue 4 ... (null) (null) (null) (null)
This is what I aim to achieve. Essentially, I am needing to see 1 of each log number id, and on the same row show the users name and status that have/haven't marked this record.
This is the best solution I have at the moment, I'll be happy to hear for any alternatives.
Purpose
The main purpose of this is to have each log number in 1 row instead of multiple and visually inspect who has marked it or whether it is blank.