USE [Database]

IF OBJECT_ID('[Functions].[same_products_join_product_types]') IS NOT NULL
  DROP FUNCTION [Functions].[same_products_join_product_types];
GO

CREATE FUNCTION [Functions].[same_products_join_product_types](
  @product_type_id AS INT
)
RETURNS TABLE
  RETURN
  WITH [result] AS (
    SELECT [id], [parent_id], [name]
    FROM [Tables].[product_types]
    WHERE [id] = @product_type_id
  )
  SELECT [P].[id] AS [product_id],
	       [P].[barcode] AS [product_barcode],
	       [P].[product_type_id],
		   [P].[name] AS [product_name],
		   [PT].[name] AS [product_type_name]
  FROM [result] AS [R]
    JOIN [Tables].[products] AS [P]
  ON [P].[product_type_id] = [R].[id]
    JOIN [Tables].[product_types] AS [PT]
  ON [PT].[id] = [P].[product_type_id]