title: SQL - How to find a value in a tree level data structure
body: <p>I have two <code>SQL Server</code> tables:</p>
  
  <ul>
  <li>Invoice (<code>invoice</code>)</li>
  <li>Invoice Relations (<code>invoice_relation</code>)</li>
  </ul>
  
  <p><code>invoice</code> table stores all invoice records with a transaction folio.</p>
  
  <p><a href="https://i.stack.imgur.com/6o3Hb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6o3Hb.png" alt="enter image description here"></a></p>
  
  <p><code>invoice_relation</code> table stores any relation between invoices.</p>
  
  <p><a href="https://i.stack.imgur.com/RQZwx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RQZwx.png" alt="enter image description here"></a></p>
  
  <p>This is an example of how invoices can be related between each other:</p>
  
  <p><a href="https://i.stack.imgur.com/f63hy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f63hy.png" alt="enter image description here"></a></p>
  
  <p>So the goal is to find the "<code>folio</code>" under <code>invoice</code> table given an <code>invoicenumber</code> and a <code>folio</code> but the <code>folio</code> sometimes won't be the <code>folio</code> that the <code>invoice</code> has, so I need to do a search on all the tree relation in order to find if any invoice match the invoice number but also the <code>folio</code> is part of the relation.</p>
  
  <p>For example I have to find the folio and match invoice number of:</p>
  
  <ul>
  <li>Folio: 1003</li>
  <li>Invoice Number: A1122</li>
  </ul>
  
  <p>In my query I would need to first find by folio because it's my <code>invoice</code> table primary key. Then, will try to match <code>A1122</code> with <code>D1122</code> that won't match, so then I have to search all the tree structure to find if there is a <code>A1122</code>. The result will be that the invoice <code>A1122</code> was found in folio <code>1000</code>.</p>
  
  <blockquote>
    <p>Any clue on how to do this?</p>
  </blockquote>
  
  <p>Here is a script of how to create the above example tables with data:</p>
  
  <pre><code>CREATE TABLE [dbo].[invoice](
      [folio] [int] NOT NULL,
      [invoicenumber] [nvarchar](20) NOT NULL,
      [isactive] [bit] NOT NULL,
   CONSTRAINT [PK_invoice] PRIMARY KEY CLUSTERED 
  (
      [folio] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  
  CREATE TABLE [dbo].[invoice_relation](
      [relationid] [int] NOT NULL,
      [invoice] [nvarchar](20) NOT NULL,
      [parentinvoice] [nvarchar](20) NOT NULL,
   CONSTRAINT [PK_invoice_relation_1] PRIMARY KEY CLUSTERED 
  (
      [relationid] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1000, N'A1122', 1)
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1001, N'B1122', 1)
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1002, N'C1122', 1)
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1003, N'D1122', 1)
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1004, N'F1122', 1)
  GO
  INSERT [dbo].[invoice] ([folio], [invoicenumber], [isactive]) VALUES (1005, N'G1122', 1)
  GO
  INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (1, N'A1122', N'B1122')
  GO
  INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (2, N'C1122', N'A1122')
  GO
  INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (3, N'D1122', N'A1122')
  GO
  INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (4, N'F1122', N'B1122')
  GO
  INSERT [dbo].[invoice_relation] ([relationid], [invoice], [parentinvoice]) VALUES (5, N'G1122', N'F1122')
  GO
  </code></pre>