How to bring columns to a table from another table by the Id?

I have this tables People, Ticket, and Report.

+----------+-------+-----+
| idPeople |  Name | Age |
+----------+-------+-----+
|        1 | Name1 |  21 |
|        2 | Name2 |  37 |
|        3 | Name3 |  28 |
+----------+-------+-----+

I would like to replace the ForeingKey idPeople with columns Name and Age from People table.

+----------+------------+------------+----------+
| idTicket | ticketCol2 | ticketCol3 | idPeople |
+----------+------------+------------+----------+
|        5 | True       | 01/06/99   |       1 |
|        6 | False      | 01/06/99   |       2 |
|        7 | True       | 01/06/99   |       4 |
+----------+------------+------------+----------+

In the Report table replace the Foreing Key idTicket with ticketCol2, Name, Age from the previous table Ticket with replaced columns (idPeople by Name, Age).

+----------+----------+------------+------------+
| idReport | idTicket | ReportCol3 | ReportCol4 |
+----------+----------+------------+------------+
|        1 |        5 | 01/06/99   | blabla     |
|        2 |        7 | 01/06/99   | asdfdd     |
|        2 |        6 | 01/06/99   | fooboo     |
+----------+----------+------------+------------+

And I the result should be like this table and must be done in one query.

+----------+------------+------------+------------+------------+------+-----+
| idReport | ticketCol2 | ticketCol3 | ReportCol3 | ReportCol4 | Name | Age |
+----------+------------+------------+------------+------------+------+-----+
|        1 | 01/06/99   | abcd       | blabla     |     123456 | Name |  20 |
|        2 | 01/06/99   | bcda       | asdfdd     |     321456 | Name |  23 |
|        3 | 01/06/99   | asdf       | fooboo     |     123456 | Name |  28 |
+----------+------------+------------+------------+------------+------+-----+

I Have tried replacing the foreingkeys with LEFT JOIN and bringing some columns Name and Age to the Ticket table but now the last part where I should replace idTicket with Columns from Ticket is not working.

I have read about the nested JOINs but I cannot understand it very well, I would really appreciate some idea of how I can do it or what should I investigate. Are nested Joins the right way?

The query that I’ve tried to accomplish the Table Ticket.

SELECT Ticket.ticketCol2, Ticket.ticketCol3, p.Name 'Name', p.Age 'Age'
       from Ticket
left join people p on (Ticket.idPeople=p.idPeople);

Answer

Try something like this:

SELECT Report.idReport, 
       Ticket.ticketCol2, 
       Ticket.ticketCol3, 
       Report.ReportCol3, 
       Report.ReportCol4, 
       People.Name, 
       People.Age 
FROM People
LEFT JOIN Ticket ON Ticket.idPeople = People.idPeople
LEFT JOIN Report ON Report.idTicket = Ticket.idTicket

Like @RiggsFolly said, the Ticket.idPeople won´t match to the People.idPeople, so this will not match any rows.

Advertisement

Answer