Wsrtjtyk

I have to get the three first results of a one to many join.
For example

JOIN `company`.`Reason`

  ON ((`company`.`Reason`.`quality_id` = `company`.`Quality`.`id`)))

where Quality has a one to many relation to Reason.

And I need to show in a column REASON_1 the first result from Reason, in REASON_2 the second and REASON_3 the third.

At the moment, I show only one row of result this way :

  `company`.`Reason`.`status`                                                AS `Reason`

And I don't know how this works, how does it select one row, and which one ?

And how can I do the same, but only with 3 rows and with different column name ?

Quality

ID | a  | b

1  | a1 | b1 

2  | a2 | b2

Reason

ID | quality_id  | status

1  | quality_id_1 | status_1 

2  | quality_id_1 | status_2

2  | quality_id_2 | status_1

2  | quality_id_2 | status_3

So if I want to get Reasons of Quality of id 1 I would get

REASON_1 | REASON_2 | REASON_3

status_1 | status_2 | null

You need to create an ordering within the reason table partitioned by the id from quality and then pivot this out on the columns from quality that you wish to return. In my code below i have done this using variables and a subquery in order to avoid having to making modifications to the base tables.

CREATE TABLE Quality (

  QualityID INT AUTO_INCREMENT,

  Value VARCHAR(50),

  PRIMARY KEY(QualityID)

  );



 CREATE TABLE Reason (

  ReasonID INT AUTO_INCREMENT,

  QualityID INT,

  Status varchar(50),

  PRIMARY KEY(ReasonID)

  );



  INSERT INTO Quality (Value) VALUES ('A1'),('A2');



  INSERT INTO Reason (QualityID, Status) VALUES (1,'Test1'),(1,'Test2'),(2,'Test3'),(2,'Test4'),(2,'Test5');



  SET @RC = 0;

SET @QC = 0;



SELECT

Q.Value,

MAX(IF(R.OrderNo = 1, R.Status, NULL)) AS Status1,

MAX(IF(R.OrderNo = 2, R.Status, NULL)) AS Status2,

MAX(IF(R.OrderNo = 3, R.Status, NULL)) AS Status3

FROM Quality Q

INNER JOIN (SELECT

@RC:=CASE

    WHEN @QC = QualityID THEN @RC + 1

    ELSE 1

END AS OrderNo,

@QC:=QualityID as QualityID,

Status

FROM Reason R)R ON R.QualityID = Q.QualityID

GROUP BY Q.Value;