-2

I have a table named tblMitigation, which has several columns:

Headcode, Time, Origin, Destination, Mitigation, Next Service.

The Next Service column refers to another Headcode value in the same table. How can I use MySQL to look up the Mitigation column for that other service?

Thanks
Chris

Philipp Maurer
  • 2,480
  • 6
  • 18
  • 25
Chris Cordner
  • 7
  • 1
  • Do you have an example ? – executable Sep 21 '18 at 09:04
  • 1
    You mean like a self join? You can join `tblMitigation` with `tblMitigation` but you should remember to put good aliases to avoid confusion – Cleptus Sep 21 '18 at 09:13
  • Has `tblmitigation.headcode` unique values, or is it possible that multiple rows have the same value? – Philipp Maurer Sep 21 '18 at 09:16
  • 1
    @ChrisCordner Hello Chris. I could see in your history, that you did not accept an answer yet. If one of the answers below answer your question please accept it. [How to accept an answer](https://stackoverflow.com/help/accepted-answer) – Philipp Maurer Sep 21 '18 at 09:19
  • 3
    Possible duplicate of [What is SELF JOIN and when would you use it?](https://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it) and al so [Explanation of self joins](https://stackoverflow.com/questions/2458519/explanation-of-self-joins) – Cleptus Sep 21 '18 at 09:19
  • @PhilippMaurer Good advice, unfortunate it is dupe and most likely will get closed because of that – Cleptus Sep 21 '18 at 09:23

1 Answers1

1

You could JOIN the table to itself. Use a LEFT JOIN in case there is no Next Service as yet. The COALESCE will ensure the value of Next_Mitigation is something different in that case e.g. N/A

SELECT current.*, COALESCE(next.Mitigation, 'N/A') AS Next_Mitigation
FROM tblMitigation current
LEFT JOIN tblMitigation next ON next.Headcode = current.`Next Service`
Cleptus
  • 3,446
  • 4
  • 28
  • 34
Nick
  • 138,499
  • 22
  • 57
  • 95