1

Due to some legacy code, I have 2 MySQL tables with the below structure (simplified): 

Invoice (ID, InvoiceNo, First_Item)

InvoiceItem (ID, Details, Next_Item)

Obviously, there are many InvoiceItems for each Invoice.

The legacy app expects you to load the Invoice row first, then load the first item from the InvoiceItem table using the Invoice's First_Item value. To get each successive InvoiceItem row, you would then follow its Next_Item value until you hit a null value.

Is there a way to write MySQL SQL that would bring back all InvoiceItem(s) for a given Invoice? i.e follow the Invoice's First_Item and then traverse all the Invoice_Items's Next_Item pointers.

Thanks Bill.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Bill Walton
  • 31
  • 1
  • 3
  • Hmm, that's a weird configuration. Basically you have a LinkedList. I'm not sure a query will help you out here. If I were you I'd make a pivot table and do a one-to-many query. – Klemen Tusar Oct 17 '19 at 13:29
  • Which version of MySQL are you using? And, if an older one, can you use a newer one? – Strawberry Oct 17 '19 at 13:44
  • @Strawberry: I am using 5.2.2, but its not a problem to upgrade. – Bill Walton Oct 17 '19 at 14:11
  • Upgrade. This problem is more straightforward in versions 8+ – Strawberry Oct 17 '19 at 14:13
  • can you create a http://sqlfiddle.com/ ? – EchoMike444 Oct 18 '19 at 04:55
  • @EchoMike444 - Good idea, thata great tool. I have set up a SQLfiddle here http://sqlfiddle.com/#!9/5b7749/3 – Bill Walton Oct 18 '19 at 10:59
  • So what I am trying to achieve is a query (or something MySQL related) that can pull back all rows for the Invoice. In this example, there should 3 rows returned for the one and only Invoice in the dataset. The first InvoiceItem returned would be the one with ID;0 (because Invoice.First_Item points to InvoiceItem with ID:0), then the second row returned would be InvoiceItem with ID:1 (as the first Invoice_Item's Next_Item points to InvoiceItem ID:1), then the third row returned would be InvoiceItem ID:2 (as the second InvoiceItem's Next_Item points to InvoiceItem ID:2). – Bill Walton Oct 18 '19 at 11:05
  • @Strawberry: "The problem is more straightforward in 8+" ? Its a pretty straight forward problem in 5.2.2 :-) What features of 8+ would help me here? – Bill Walton Oct 18 '19 at 11:06
  • Pre 8, MySQL has no native support for recursion. Post 8, that changed. I don't use 8 (yet) so I'm not best qualified to answer further. – Strawberry Oct 18 '19 at 12:18

1 Answers1

1

You want do a recursive query , but mysql < 8 does not support it .

This is a solution that work on you small dataset ( from sqlfiddle ) 

select  id,
        details , 
        next_item
from    (select * from invoiceitem
         order by id ) inv_itm,
        (select @iis := 0 )  init
where   find_in_set(id, @iis)
and not find_in_set(9999999999, @iis)
and     length(@iis := concat(@iis, ',', ifnull(next_item,9999999999))) ;

This solution will work only if for each invoices id of items are in ascending order .

This solution is inspired by How to create a MySQL hierarchical recursive query

You need to plan a upgrade to 5.7 or 8.0 , because bellow you will have no security update soon .

see https://en.wikipedia.org/wiki/MySQL#Release_history

EchoMike444
  • 1,513
  • 1
  • 9
  • 8