How to return unique objects with matching foreign key values?
<p>I have a split select statement where I'm aiming in first <code>SELECT</code> to return all the unique dashboards for a given email.</p>

<p>Then a second <code>SELECT</code> to return the associated Charts and KPI foreign keys matching the ID of the unique dashboard.</p>

<p>So what I did try is using a <code>UNION</code> between the two selects, which gives an error <code>All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists</code>.</p>

<p>I did <a href="https://stackoverflow.com/questions/20299390/error-union-operator-must-have-an-equal-number-of-expressions-when-using-cte">come across a similar issue</a> here but the solution of adding an even number of column values between selects doesn't suit this situation.</p>

<p><strong>Question:</strong></p>

<p>How can you return unique objects with matching foreign key values?</p>

<p>This is the current procedure I came up with but I'm open to better alternatives:</p>

<pre><code>ALTER PROCEDURE [dbo].[GetUserProfile]
   @p_email VARCHAR(100)
AS 

   BEGIN     

          SELECT UserName, Email, Dashboard_Name, RID from [dbo].[User]
              inner join [dbo].[Dashboard]  on
              [Dashboard].[USER_ID]=[User].Email
              and Email=@p_email    
          UNION
              SELECT KPI_Name, Chart_Name FROM [KPI]
          inner join [dbo].[Chart] on
              [Chart].[KPI_ID]=[KPI].ID

   END
</code></pre>

<p>Also this is a gist of the four tables, foreign key constraints have been removed for brevity:</p>

<pre><code> TABLE [dbo].[User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Email] [varchar](80) NOT NULL,        


TABLE [dbo].[Dashboard](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Dashboard_Name] [varchar](100) NOT NULL,
[RID] [nvarchar](255) NOT NULL,
[USER_ID] [varchar](80) NOT NULL


TABLE [dbo].[KPI](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KPI_Name] [varchar](100) NOT NULL,
[DashboardID] [int] NOT NULL,


TABLE [dbo].[Chart](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Chart_Name] [varchar](100) NOT NULL,
[KPI_ID] [int] NOT NULL,
</code></pre>

<p>So this would be the expected outcome if the query is correct:</p>

<pre><code>{  
   UserName:"brian",
   Email:"brian@gmail.com",
   Dashboard_Name:"test dash 1",
   RID:"2003",
   DashboardID:1,
   KPI_Name:"test kpi 1",
   KPI_ID:1,
   Chart_ID:1,
   Chart_Name:"ch1, ch2, ch3"
},
{  
   UserName:"brian",
   Email:"brian@gmail.com",
   Dashboard_Name:"test dash 2",
   RID:"2003",
   DashboardID:2,
   KPI_Name:"test kpi 2",
   KPI_ID:2,
   Chart_ID:2,
   Chart_Name:"ch4, ch5, ch6"
},
</code></pre>