Power Platform Community Forum Thread Details

Here is a practical path you can follow. Try this approach and This is the example to start

Connect• Use SQL Server Management Studio or Azure Data Studio.• Create a read-only login.• In SSMS, Connect, enter Server name, choose SQL Authentication or Windows, then Connect.
Find the right tables and keysRun these to see columns and relationships.

-- List tables
SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY s.name, t.name;

-- Columns for a table
EXEC sys.sp_help 'dbo.Orders';

-- Foreign keys in the database
SELECT fk.name, OBJECT_NAME(fk.parent_object_id) AS child_table,
       cpa.name AS child_col, OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       cref.name AS parent_col
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns cpa ON cpa.object_id = fkc.parent_object_id AND cpa.column_id = fkc.parent_column_id
JOIN sys.columns cref ON cref.object_id = fkc.referenced_object_id AND cref.column_id = fkc.referenced_column_id
ORDER BY child_table, fk.name;

Link tables with JOINsPick the key fields from step 2. Then write targeted joins.

Inner join, one to many:

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM dbo.Orders o
JOIN dbo.Customers c
  ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2025-01-01';

Left join to keep all rows from the left side:

SELECT c.CustomerID, c.CustomerName, o.OrderID
FROM dbo.Customers c
LEFT JOIN dbo.Orders o
  ON o.CustomerID = c.CustomerID;

Many to many through a bridge table:

SELECT p.ProductID, p.ProductName, c.CategoryName
FROM dbo.Products p
JOIN dbo.ProductCategory pc
  ON pc.ProductID = p.ProductID
JOIN dbo.Categories c
  ON c.CategoryID = pc.CategoryID;

Composite key:

SELECT f.FactID, d.CalendarDate, d.FiscalYear
FROM dbo.FactSales f
JOIN dbo.DimDate d
  ON d.CalendarDate = f.CalendarDate
 AND d.FiscalYear  = f.FiscalYear;

Across databases on the same server:

SELECT *
FROM SalesDB.dbo.Orders o
JOIN MasterData.dbo.Customers c ON c.CustomerID = o.CustomerID;

Across servers with a linked server:

SELECT *
FROM [LOCALSERVER].SalesDB.dbo.Orders o
JOIN [LINKEDSRV].MasterData.dbo.Customers c ON c.CustomerID = o.CustomerID;

Pull only what you need• List columns, avoid SELECT *.• Filter early with WHERE.• Use table aliases.• Always schema-qualify names.
Check performance• Ensure join columns are indexed on both sides.• Look for scans on large tables.• Add covering indexes if a query is frequent.
Validate results• Count expected rows before and after joins.• Watch for duplicates from unintended many to many joins.

-- Quick cardinality check
SELECT COUNT(*) FROM dbo.Orders;           -- base
SELECT COUNT(*) FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID;  -- after join

If you share two table names and the fields you want to link, I will draft the exact JOIN with filters and indexes.

Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!