How to return unique objects with matching foreign key values?

Question

I have a split select statement where I'm aiming in first SELECT to return all the unique dashboards for a given email.

Then a second SELECT to return the associated Charts and KPI foreign keys matching the ID of the unique dashboard.

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

I did come across a similar issue here but the solution of adding an even number of column values between selects doesn't suit this situation.

Question:

How can you return unique objects with matching foreign key values?

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

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

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

 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,

So this would be the expected outcome if the query is correct:

{  
   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"
},

score 1 · Accepted Answer · answered Nov 10 '16 at 18:23

Your Union won't work as stated above. Try this.

SELECT UserName, Email, Dashboard_Name, RID, Dashboard.ID as Dashboard_ID, KPI_Name, KPI.ID as KPI_ID,Chart.ID as Chart_ID, Chart_Name
  from [User]
  inner join [Dashboard]  on [Dashboard].[USER_ID]=[User].Email
              and [User].Email=@p_email 
  Inner Join [KPI] ON [Dashboard].[Dashboard_ID] = [KPI].[Dashboard_ID]
  Inner Join [Chart] ON [KPI].[ID] = [Chart].[KPI_ID]

score 0 · Answer 2 · answered Nov 10 '16 at 18:18

This shoud help; I'm used to oracle syntax so I'm not sure if you need more or less branches:

ALTER PROCEDURE [dbo].[GetUserProfile]
   @p_email VARCHAR(100)
AS 
BEGIN
DECLARE
  vUserName [varchar](50);
  vEmail [varchar](80);
  vDashboard_Name [varchar](100);
  vRID [nvarchar](255); 
  vDashboardId [int];
  vKPI_Name [varchar](100);
  vChart_Name [varchar](100);
BEGIN
  SELECT U.UserName, U.Email, D.Dashboard_Name, 
         D.RID, D.ID
  INTO  vUserName, vEmail, vDashboard_Name, 
        vRID, vDashboardId
  FROM  [dbo].[User] U   INNER JOIN [dbo].[Dashboard] D  ON D.[USER_ID]=U.Email
  WHERE U.Email=@p_email;    

  SELECT K.KPI_Name, C.Chart_Name 
  INTO vKPI_Name, vChart_Name 
  FROM [KPI] K INNER JOIN [dbo].[Chart] C ON C.[KPI_ID]=K.ID
  WHERE K.DashboardID = vDashboardId;
END;
END;

How to return unique objects with matching foreign key values?

2 Answers2