Inner Join is not correct and don't want to use If-else

This is related to this question https://www.experts-exchange.com/questions/29129555/I-have-a-solution-Inner-Join-but-I-also-need-all-the-rows.html
Scott and Kyle helped me with it but now, my moody manager changed things around on me and I'm again stuck on coming up with a similar solution to the question above. My manager says to use if-else but I think there's a better way of doing this (the link above has the solution but I don't know how to apply it to below)

I know
1. I need to start with #AuthUser table
2. I need to look at FilterML and FilterPortfolio columns in #AuthUser

The change I need
User 14307 has FilterML and FilterPortfolio set to 0. This means there's no filter, so if I run the query below, I should get rows back but I don't. I know it's because I do inner join and look at the tables to see if user is in there or not and that's not correct.

It works fine for user 88. run it for SET @userid = 88 -- 14307 and then change it to 14307 and run it again and you don't see any rows but I should get rows back because MLFilter and MLPOrtfolio are false for user 14307...this user has access to everything, no filtering should be applied to it.

  CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterML BIT,
  FilterPortfolio bit
)

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterML,
	FilterPortfolio
)
SELECT 88, 'Edward', 1,1 -- has filter and will get data with the INNER JOIN I have because #Portfolios_User_Has_Access_To table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
     FilterML,
	FilterPortfolio
)
SELECT 14307, 'Camillia', 0,0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #Portfolios_User_Has_Access_To doesn't get populated)

---------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #AuthUserPortfolio -- users and portfolios they have access to. Some users are in this table and some aren't. User 88 has rows User 14307 doesn't
(
  AuthUserId INT,
  PortfolioId int
)

INSERT INTO #AuthUserPortfolio
(
    AuthUserId,
    PortfolioId
)
SELECT 88,1

INSERT INTO #AuthUserPortfolio
(
    AuthUserId,
    PortfolioId
)
SELECT 88,2

-----------------------------------------------------------------------------------------------------------------------

CREATE TABLE #AuthUserMasterLease --users that have access to MasterLease. Some users like user 88 has rows. Some users like user 14307 doesn't 
(
  AuthUserId INT,
  MasterLeaseId int
)

INSERT INTO #AuthUserMasterLease
(
    AuthUserId,
    MasterLeaseId
)
SELECT 88,240

INSERT INTO #AuthUserMasterLease
(
    AuthUserId,
    MasterLeaseId
)
SELECT 88,243


-----------------------------------------------------------------------------------------------------------------------

CREATE TABLE #masterlease --master leases for all users
(

  id INT,
  portfolioid int
)

INSERT INTO #masterlease
(
    id,
	portfolioid
)
SELECT 240,1

INSERT INTO #masterlease
(
    id,
	portfolioid
)
SELECT 243,2

--------------------------------------------------------------------------------------------------------------------

CREATE TABLE #schedule --- scheules for all users
(
 id INT,
 masterleaseid int
)

INSERT INTO #schedule
(
    id,
    masterleaseid
)
SELECT 1666,240


INSERT INTO #schedule
(
    id,
    masterleaseid
)
SELECT 3740,243

--------------------------------------------------------------------------------------------------------------

CREATE TABLE #Portfolios_User_Has_Access_To -- hold users and the portfolios they have access to
(
   mlportId int
)


-------------------------
DECLARE @userid INT
SET @userid = 88 -- 14307

INSERT INTO #Portfolios_User_Has_Access_To
(
  
    mlportId
)

SELECT   
    
      aup.PortfolioId
	   FROM 
	
#AuthUserPortfolio aup --*** I have to look at AuthUser and FilterPortfolio flag
WHERE aup.AuthUserId = @userId  --****** user 88 brings back rows but user 14307 doesn't **************

------

 SELECT 
 s.id AS schedId,
 ml.id AS MLID,
 ml.PortfolioID
   FROM #Schedule s
       INNER JOIN #MasterLease ml ON ml.ID = s.MasterLeaseID
	   INNER JOIN #Portfolios_User_Has_Access_To p ON p.mlportId = ml.PortfolioID --only get the portfolios user has access to from ML table
       INNER JOIN #AuthUserMasterLease auml ON auml.MasterLeaseId = ml.ID ---*** I'm inner joining but user 14307 doesn't have a row in AuthUserMasterLease
WHERE auml.AuthUserId IN (SELECT id FROM dbo.AuthUser WHERE  id= @userId) --- This isn't correct. I have to look at AutUser and FilterML flag

DROP TABLE #Authuser
DROP TABLE #AuthUserMasterLease
DROP TABLE #AuthUserPortfolio
DROP TABLE #masterlease
DROP TABLE #Portfolios_User_Has_Access_To
DROP TABLE #schedule

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
From the previous question
The reason why both our solutions work is we get the schedules based off the user.  Yours you are getting the schedules and consider the user after the fact.

You have to change the order of selects to implement either solution.
bold emphasis added

There is repeated advice in the previous question on how to tackle this problem, yet you aren't adopting that advice

 SELECT
 s.id AS schedId,
 ml.id AS MLID,
 ml.PortfolioID
  FROM #Schedule s << Why are you not using AuthUser here?
CamilliaAuthor Commented:
Let me try it again (I'm so mentally burned out and frustrated by this and my manager's moodiness and him keep changing his mind)

But what about the first SQL? How can I do this one? Should I just go with If-else?

DECLARE @userid INT
SET @userid = 88 -- 14307

INSERT INTO #Portfolios_User_Has_Access_To
(
  
    mlportId
)

SELECT   
    
      aup.PortfolioId
	   FROM 
	
#AuthUserPortfolio aup --*** I have to look at AuthUser and FilterPortfolio flag
WHERE aup.AuthUserId = @userId  --****** user 88 brings back rows but user 14307 doesn't **************

Open in new window

slightwv (䄆 Netminder) Commented:
I've read both questions several times now and I have to admit that I'm really confused.  The original question seemed to jump around requirements and add new tables that don't seem to exist in this one.  Like Main and FilterSchedule.

I think you are making this much harder than it really needs to be.

I assume the temp tables are examples of the real tables involved?  If not, are you creating temp tables as part of trying to find an answer? If so, I don't believe they are necessary.

I'm thinking you need to go back to left joins and add filtering in the where clause suggested in the other question.



It seems portfolios are defined in the masterlease table?  I'm not understanding the reason for the two filter columns,   FilterML  and  FilterPortfolio in the authuser table.

Is there some requirement there a user can see an individual lease outside the portfolio table?

As I understand things, I see no need for the Portfolios_User_Has_Access_To table.

You've offered to post better sample data.  Can you?  If you have added tables to the initial problem, can you remove those?

I would also like a little deeper understanding of how the tables relate.  For example what is the schedule table and how does it relate and what is a portfolio?  I see an id for a portfolio but no table.

I assume there is a one to many between masterlease and portfolio?
So I can have two leases in one portfolio:
INSERT INTO masterlease values( 240,1);
INSERT INTO masterlease values( 999,1);

I would like to get back to the basics and not try to build on all previous attempts.  Once you've started down a hole, some times it is best to back out and start a new hole.

Can you pretend you've never tried anything before and go back to the original base tables with sample data and expected results for as many different user types as you can have.

Include some "other" data like I used in my example above to confirm Camilla can actually see everything and Edward doesn't see things he shouldn't.

Like:
insert into AuthUserMasterLease values(99,244)

If you had an table relationship diagram for the tables involved you could share, it would be great.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

CamilliaAuthor Commented:
I assume the temp tables are examples of the real tables involved?

Yes, that's correct.

As I understand things, I see no need for the Portfolios_User_Has_Access_To table.
Probably but I want to make it work as is to get it going. Then I can remove what's not needed

Include some "other" data like I used in my example above to confirm Camilla can actually see everything and Edward doesn't see things he shouldn't.

Camilla user has to bring back the same rows. It's not as I have it now because I'm inner joining to #AuthUserPortfolio and #AuthUserMasterLease and user 14307 doesn't have rows in there and Filter columns in AuthUser for it are zero...meaning all rows need to be brought back regardless of #AuthUserPortfolio and #AuthUserMasterLease


I'm thinking you need to go back to left joins and add filtering in the where clause suggested in the other question.
Let me try the solution in my orig question but that doesn't take care of the first SQL I posted here
https://www.experts-exchange.com/questions/29130828/Inner-Join-is-not-correct-and-don't-want-to-use-If-else.html#a42764748


I think you are making this much harder than it really needs to be.

I'm sure I am because I'm exhausted by lack of direction from my moody manager.

I'll work on it this weekend and post back.
slightwv (䄆 Netminder) Commented:
>>Yes, that's correct.

But what set of temp tables?  Between the two questions you have different ones.  You also admitted that you have created some that aren't part of the core question just in case you might need them.

>>Probably but I want to make it work as is to get it going. Then I can remove what's not needed

Why waste the effort and over complicate things more than necessary?

You want to boil an egg.  Why mine and smelt metal to make a new pot just in case the 10 pots you already have don't work?

What you have tried to describe seems like a fairly straight forward query.  You have some users that can see everything ans some that can only see a few things.  Most of us here have written several of these queries over the years.

We just need to understand your core data structures and the results your Manager wants.  Posting things you have already tried and added isn't helping us and is not only confusing you but us as well.

Before you were assigned this task, I assume the database already existed.  If so, please provide the original tables, some sample data from those tables and your expected results.

Forget everything you have tried to date and anything you might have added.

I want to start over at the beginning like your Manager gave me the task instead of you.

I asked how the tables relate to each other and you didn't provide that information.
CamilliaAuthor Commented:
I'll post the tables with some sample data. I'll post the actual ones and not the temps I've created as sample here.

I'll post back.
CamilliaAuthor Commented:
I typed up everything but didn't get posted. Let me try again. I think the scripts were too large. I'll post back again.
CamilliaAuthor Commented:
I'll go step by step. These are the real tables with sample data. For security, I've changed some data

1. This is AuthUsers. Has all the users and the "filter" columns that are either true or false.
User 88 has filters for 3 tables. User 14307 has no filters which means has to get all the data and

CREATE TABLE [dbo].[AuthUser](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](64) NOT NULL,
	[Username] [nvarchar](128) NOT NULL,
	[Email] [nvarchar](128) NOT NULL,
	[FirstName] [nvarchar](32) NULL,
	[LastName] [nvarchar](32) NULL,
	[FilterPortfolios] [bit] NOT NULL,
	[FilterSchedules] [bit] NOT NULL,
	[FilterMasterLeases] [bit] NOT NULL,
	[FilterUDFs] [bit] NOT NULL,

 CONSTRAINT [PK__AuthUser1__3214EC2702084FDA] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AuthUser] ON 
GO

INSERT [dbo].[AuthUser] ([ID], [Name], [Username], [Email], [FirstName], [LastName], [FilterPortfolios], [FilterSchedules], [FilterMasterLeases], [FilterUDFs]) VALUES (88, N'Edward C', N'Edward.c@.com', N'88@.com', N'Edward', N'C', 1, 1, 1, 0)
GO

INSERT [dbo].[AuthUser] ([ID], [Name], [Username], [Email], [FirstName], [LastName], [FilterPortfolios], [FilterSchedules], [FilterMasterLeases], [FilterUDFs]) VALUES (14307, N'Camilla Test', N'camilla.T@.com', N'14307@.com', N'Camilla', N'Test', 0, 0, 0, 0)
GO

Open in new window


2. This is the Portfolio table. Has the country names.

/****** Object:  Table [dbo].[Portfolio]    Script Date: 12/29/2018 8:16:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Portfolio](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](256) NULL,
	[ShortName] [varchar](5) NULL,
	[DefaultCultureId] [int] NOT NULL,
	[IsoCode] [char](3) NULL,
	[SourceSystemID] [int] NULL,
	[PortfolioID_SrcPK] [int] NULL,
	[API_CodeInSchedule] [nvarchar](125) NULL,
 CONSTRAINT [PK_Portfolio] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Portfolio] ON 
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (1, N'USA', N'US', 4, N'USA', 1, 1, N'US|USA')
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (2, N'Canada', N'CA', 2, N'CAN', 1, 2, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (3, N'United Kingdom', N'UK', 3, N'GBR', 2, 1, N'UK')
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (4, N'India', N'IN', 3, N'IND', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (5, N'Norway', N'NO', 3, N'NOR', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (6, N'Sweden', N'SE', 3, N'SWE', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (7, N'Czech Republic', N'CZ', 31, N'CZE', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (8, N'Slovakia', N'SK', 30, N'SVN', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (9, N'Poland', N'PL', 29, N'POL', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (10, N'Portugal', N'PT', 28, N'PRT', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (11, N'Germany', N'DE', 6, N'DEU', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (12, N'Italy', N'IT', 27, N'ITA', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (13, N'Spain', N'ES', 5, N'ESP', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (14, N'France', N'FR', 7, N'FRA', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (15, N'Brazil', N'BR', 26, N'BRA', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (16, N'Chile', N'CL', 25, N'CHL', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (17, N'Colombia', N'CO', 22, N'COL', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (18, N'Peru', N'PE', 21, N'PER', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (19, N'Costa Rica', N'CR', 20, N'CRI', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (20, N'El Salvador', N'SV', 19, N'SLV', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (21, N'Guatemala', N'GT', 18, N'GTM', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (22, N'Panama', N'PA', 17, N'PAN', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (23, N'Puerto Rico', N'PR', 16, N'PRI', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (24, N'Dominican Republic', N'DO', 15, N'DOM', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (25, N'Honduras', N'HN', 14, N'HND', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (26, N'Mexico', N'MX', 13, N'MEX', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (27, N'China', N'CN', 12, N'CHN', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (28, N'Hong Kong', N'HK', 11, N'HKG', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (29, N'Singapore', N'SG', 33, N'SGP', NULL, NULL, N'SG')
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (30, N'Malaysia', N'MY', 32, N'MYS', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (31, N'Australia', N'AU', 10, N'AUS', NULL, NULL, N'AU|AUS')
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (32, N'New Zealand', N'NZ', 10, N'NZL', NULL, NULL, NULL)
GO
INSERT [dbo].[Portfolio] ([ID], [Name], [ShortName], [DefaultCultureId], [IsoCode], [SourceSystemID], [PortfolioID_SrcPK], [API_CodeInSchedule]) VALUES (33, N'Nicaragua', N'NI', 61, N'ESI', NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Portfolio] OFF
GO
ALTER TABLE [dbo].[Portfolio] ADD  CONSTRAINT [DF_Portfolio_DefaultCulture]  DEFAULT ((4)) FOR [DefaultCultureId]
GO
ALTER TABLE [dbo].[Portfolio]  WITH NOCHECK ADD  CONSTRAINT [FK_Portfolio_Culture] FOREIGN KEY([DefaultCultureId])
REFERENCES [dbo].[Culture] ([ID])
GO
ALTER TABLE [dbo].[Portfolio] CHECK CONSTRAINT [FK_Portfolio_Culture]
GO
ALTER TABLE [dbo].[Portfolio]  WITH NOCHECK ADD  CONSTRAINT [FK_Portfolio_SourceSystemID] FOREIGN KEY([SourceSystemID])
REFERENCES [dbo].[SourceSystem] ([ID])
GO
ALTER TABLE [dbo].[Portfolio] CHECK CONSTRAINT [FK_Portfolio_SourceSystemID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Used to contain series of pipe delimited items indicating the Portfolio of a Sched field when submitted via the API.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portfolio', @level2type=N'COLUMN',@level2name=N'API_CodeInSchedule'
GO

Open in new window


3. These are the Filter tables. User 88 has rows in them. It means user 88 is only limited to this data (related tables below). User 14307 doesn't have rows in these tables which means there's no limited data for this user.

Note: I have all the data since there's no identifying data
 
Please see attached file name FilterTables.

4.  This is the MasterLease table. User has access to these based on FilterMastersLeases column in AuthUser table above. If column is 1, user has rows in AuthMasterLease table that corresponds to rows in MasterLease table. If user's FilterMasterLeases is 0, user doesn't have any filtering so has access to all the rows in MasterLease table

Please see MasterLease file attached.

5. This is the schedule table . It relates to the MasterLease table by MasterLeaseId. If user has FilterSchedules in Authuser as 1, it means there's filtering and user has rows in AuthUserSchedules and has access to limitd Schedules. If user has FilterSchedules in AuthUser as 0, then user has no rows in AuthUserSchedules and there's no filtering

Please see Schedule file attached


6. This is the unit table. Relates to the Shcedule table.
Please see Unit file attached


Now, this is how Schedule, MasterLease and Unit are related to each other

.....
  FROM [dbo].[Unit] AS [Extent1]
            INNER JOIN [dbo].[Schedule] AS [Extent2]
                ON [Extent1].[ScheduleID] = [Extent2].[ID]
            INNER JOIN [dbo].[MasterLease] AS [Extent3]
                ON [Extent2].[MasterLeaseID] = [Extent3].[ID]

Open in new window


this is what I need done
First, see what Portfolio user has access (using FilterPortfolio column in AuthUser and AuthUserPorfolio table) to and filter that. If there's no filtering, then user has access to every portfolio
Second, see what Masterlease user has access to (using FilterMasterLease column in AuthUser and AuthUserPortfolio table) and filter that. If user has no filtering, then user has access to everything
Third see what Schedule user has access to (using FilterScheudle column in AuthUser and AuthUserSchedule table) and filter. If user has no filtering, then user has access to everything

forth see what Units user has access to (using FilterUDF and AuthUserFilter table).  

My manager says it has to be done in that order because that's how we limit the results.

This is what I have now. I get correct rows for user 88. But for user 14307, I don't get anything back (What I have needs more work, I know)

 CREATE TABLE #MLS_user_has_access_to
 (
   scheduleid INT,
   mlid INT,
   mlportId int
 )

  CREATE TABLE #Scheds_And_Mls_User_Has_Access_To
 (
   schedid INT,
   MLId int
  )

  CREATE TABLE #Portfolios_User_Has_Access_To
 (
  -- scheduleid INT,
  -- mlid INT,
   mlportId int
 )


 ----- Hold rows found for searc
 CREATE TABLE #FoundMasterLeases
(
  MLId Int
)

CREATE TABLE #FoundSchedules
(
 ScheudleId int
)

 --------------------------------------------------------------------------------------------------

 DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892
 DECLARE  @searchtext nvarchar(4000)
 DECLARE @userId INT

 SET @searchtext = '%019%'
 SET @userId = 88

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

 
 ---------------------------------------------------------------------------------------------------

 --takes care of portfolio ---*** this should #1
INSERT INTO #Portfolios_User_Has_Access_To
(
  
    mlportId
)

SELECT   
     -- scheduleid,
     -- mlid,
      aup.PortfolioId
	   FROM 
	
dbo.AuthUserPortfolio aup --ON s.mlportId = aup.PortfolioId
WHERE aup.AuthUserId = @userId


--******** 2
 ----------------------------------------------------------------------------------------------------

 INSERT INTO --- takes care of master lease #2. I have the portfilio user has access to here. This only gets me the master leases, no schedules
  #MLS_user_has_access_to
  (
      scheduleid,
      mlid,
      mlportId
  )
 SELECT 
 s.id AS schedId,
 ml.id AS MLID,
 ml.PortfolioID
   FROM dbo.Schedule s
       INNER JOIN dbo.MasterLease ml ON ml.ID = s.MasterLeaseID
	   INNER JOIN #Portfolios_User_Has_Access_To p ON p.mlportId = ml.PortfolioID --only get the portfolios user has access to from ML table
       INNER JOIN dbo.AuthUserMasterLease auml ON auml.MasterLeaseId = ml.ID
WHERE auml.AuthUserId IN (SELECT id FROM dbo.AuthUser WHERE  id= @userId)
and
(ml.[Deleted] <> 1) AND (ml.[Active] = 1)
--AND  (ml.[CustomerName] LIKE @searchtext )


------------------------------------------------------------------------------------------------------------------
--********* 3
-- take care of scheules user has access to #3
INSERT INTO #Scheds_And_Mls_User_Has_Access_To
(
    schedid,
	MLId
)

SELECT   
      aus.scheduleid,
      [MasterLeaseID]
     -- mlid,
    --  mlportId 
	 -- FROM #portsuserhasaccesto p --takes care of portfolio
--INNER JOIN
FROM  dbo.AuthUserSchedule aus --ON aus.ScheduleId = p.scheduleid
INNER JOIN [dbo].[Schedule] s ON s.ID = aus.ScheduleId
INNER JOIN #MLS_user_has_access_to mls ON mls.scheduleid = s.ID --do I need this?
WHERE aus.AuthUserId = @userId
and (s.[Deleted] <> 1) AND (1 <> s.[Status]) 
 --AND
 --     (
 --         (s.[Sched] LIKE @searchtext)
      
 --         OR 
	--	  (s.[LesseeName] LIKE @searchtext)
 --     )

 ---********* up to here, we have a table of with No filters *********** -->>>> #SchedsAndMlsUserHasAccesTo -->>> correct rows for user 88 49,457 rows
---------------------------------------------------------------------------------
--4 -->>> select * FROM  #Scheds_And_Mls_User_Has_Access_To --->>> we have the list here 
--a. search master lease

INSERT INTO #FoundMasterLeases
(
    MLId
)

SELECT Id --AS MLId 
 FROM dbo.MasterLease ml

 WHERE 
 Id IN (SELECT MLID FROM #Scheds_And_Mls_User_Has_Access_To)
 AND
(ml.CustomerName LIKE @searchtext)

----b. search schedule

INSERT INTO #FoundSchedules
(
    ScheudleId
)

SELECT id --AS scheudleid
FROM dbo.Schedule s
WHERE id IN (SELECT schedid FROM #Scheds_And_Mls_User_Has_Access_To)
 AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      )

--c. get a smaller set of schedulIds

--***now unit that will get us to UDF filtering
-- **** 5 --->>> belw needs to change ***************** ** Add AuthUDFFilter
--5a. Add AuthUDFFilter . for now, tets it for 88 without AuthUserFilter *********************
--5b. Join MLFound and SchedFound tables


-- DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892
-- DECLARE  @searchtext nvarchar(4000)
-- DECLARE @userId INT

-- SET @searchtext = '%019%'
-- SET @userId = 88

--INSERT INTO @results
--	SELECT @searchtext UNION ALL
--	SELECT REPLACE(@searchtext,'0','O') UNION ALL
--	SELECT REPLACE(@searchtext,'O','0')

SELECT u.* 

FROM 
--dbo.AuthUser au 
--LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
--INNER JOIN 
dbo.Unit u 
INNER JOIN #Scheds_And_Mls_User_Has_Access_To s ON s.schedid = u.ScheduleID --- this is probably wrong. No, don't think it's wrong
WHERE
u.[Deleted] <> 1
AND
(
 (u.[CustomerRef] LIKE @searchtext)
          OR (u.[Model] LIKE @searchtext)
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext)
		 OR exists (Select 1 from @results r WHERE u.SerialNo LIKE @searchtext)
)
AND  scheduleid NOT IN  (SELECT ScheudleId  FROM #FoundSchedules) --***** we don't want dups of what we get searching schedules
---*** exclude MLFound rows as well

--**maybe union this with above

SELECT u.* FROM unit u
INNER JOIN #FoundSchedules s ON s.ScheudleId = u.ScheduleID
WHERE u.[Deleted] <> 1

Open in new window



***********  this stored proc works but I have a table called BigAssetSearch by flatteing and dumping MasterLease, Schedule and Unit data into that BigAssetSearch table. Even tho my manager agreed to this, now he says he doesn't want it and I have to do like above. Look at the bottom of the stored proc and the left joins. That's how I need to do it to get data for users with and without filters but it has to be in order to filter data step by step *****************

/****** Object:  StoredProcedure [dbo].[usp_SearchAssets]    Script Date: 11/29/2018 10:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[usp_SearchAssetsTest]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN



---*** how to call it
-- exec [dbo].[usp_SearchAssetsTest] 'nnn', 14503
-- exec  [dbo].[usp_SearchAssetsTest] 'MX7T11243364', 14307
-- exec  [dbo].[usp_SearchAssetsTest] '019', 88
-- exec  [dbo].[usp_SearchAssetsTest] '019', 14307


--DECLARE  @AssetType nvarchar(4000)--,  in code for now. ** comment later 
--DECLARE  @searchtext nvarchar(4000),
--declare @searchtext2 nvarchar(4000)

--DECLARE @userId INT --*** pass this in

--SET @userId = 88 -- 14307  --*** remove and pass in *************

 --SET @AssetType = 'N/A - Non-Managed' --**comment later
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 --SET @searchtext2 = '%MX7T11243364%'--'%O19%' --**************** remove *******************************************

 --account for 0 and O that code has. Ex: if user enters 0019, code checks for 0019, OO19. Another example, if user enters 0O19, code checks for 0O19, 0019 and OO19, and so on
DECLARE @results TABLE(SearchText NVARCHAR(4000)) --tfs892

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


 --*** break down the search for Unit, Schedules and ML for speed ***
 
 --*** unit search. First do unit search and get the data
INSERT INTO #unitsearch
(
    rowid
)
SELECT  
 rowid

FROM

	 dbo.BigAssetSearch AS u
	
where
	CHARINDEX(@searchtext,u.[CustomerRef]) > 0
	OR CHARINDEX(@searchtext,u.[Model] )> 0
	OR CHARINDEX(@searchtext,u.[Manufacturer] )> 0
	OR CHARINDEX(@searchtext,u.[SiteAddress]) > 0
	
	OR exists (Select 1 from @results r WHERE CHARINDEX(@searchtext,u.SerialNo) > 0) --account for 0 and O that C# code has


--SELECT * FROM #unitsearch

----Schedule search. We don't want dups from #unitsearch
INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
      AND
      (
	      CHARINDEX(@searchtext,s.[Sched]) > 0
         -- (s.[Sched] LIKE @searchtext)
      
          OR 
		  CHARINDEX(@searchtext,s.[LesseeName]) > 0
		 -- (s.[LesseeName] LIKE @searchtext )
      )

	-- SELECT * FROM #schedsearch

----master lease search. Dont want dups from #unitsearch and #schedsearch
INSERT INTO #MLsearch
(
    rowid
)
SELECT m.rowid
FROM dbo.BigAssetSearch m
LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
WHERE
us.rowid IS NULL
AND 
sc.rowid IS NULL
and 
		   --(m.[CustomerName] LIKE @searchtext )
		   CHARINDEX(@searchtext,m.[CustomerName]) > 0

-- SELECT * FROM #MLsearch

----result
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 
		  
FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID -- duplicate code's filtering code
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	AuthUserFilter audf ON audf.UserId = au.ID 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) --what code is doing : check for permission. If user doesn't have filters, bring everything back -->ignore filter
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
									  AND (au.FilterUDFs = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.UdFilterValue)) -- duplicate what code has for filtering UDFs 
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID -- bring back portfolio name for grid
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR --get the rows from temp table
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid -- filter by user (what C# code does)
	 AND     (au.FilterUDFs = 0 OR (au.FilterUDFs = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID))); -- duplicate what code has for filtering UDFs 

 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch


 End

Open in new window


Thanks for helping with this. As I typed this, it doesn't seem difficult. I'll work on it on Sunday some more. I need to show something to my manager on Monday.
FilterTablesEE.txt
Masterlease.txt
Schedule.txt
unit.txt
PortletPaulEE Topic AdvisorCommented:
I have setup a db<>fiddle here but it only has a small selection of rows.

Not sure if this will help, note may not get back to this for a while:
AuthUser
+-------+--------------+----------------+------------+-----------+----------+------------------+-----------------+--------------------+------------+
|  ID   |     Name     |    Username    |   Email    | FirstName | LastName | FilterPortfolios | FilterSchedules | FilterMasterLeases | FilterUDFs |
+-------+--------------+----------------+------------+-----------+----------+------------------+-----------------+--------------------+------------+
|    88 | Edward C     | Edward.c@.com  | 88@.com    | Edward    | C        | True             | True            | True               | False      |
| 14307 | Camilla Test | camilla.T@.com | 14307@.com | Camilla   | Test     | False            | False           | False              | False      |
+-------+--------------+----------------+------------+-----------+----------+------------------+-----------------+--------------------+------------+

Portfolio

+----+----------------+-----------+------------------+---------+----------------+-------------------+--------------------+
| ID |      Name      | ShortName | DefaultCultureId | IsoCode | SourceSystemID | PortfolioID_SrcPK | API_CodeInSchedule |
+----+----------------+-----------+------------------+---------+----------------+-------------------+--------------------+
|  1 | USA            | US        |                4 | USA     |              1 |                 1 | US|USA             |
|  2 | Canada         | CA        |                2 | CAN     |              1 |                 2 |                    |
|  3 | United Kingdom | UK        |                3 | GBR     |              2 |                 1 | UK                 |
|  4 | India          | IN        |                3 | IND     |                |                   |                    |
|  5 | Norway         | NO        |                3 | NOR     |                |                   |                    |
|  6 | Sweden         | SE        |                3 | SWE     |                |                   |                    |
|  7 | Czech Republic | CZ        |               31 | CZE     |                |                   |                    |
|  8 | Slovakia       | SK        |               30 | SVN     |                |                   |                    |
|  9 | Poland         | PL        |               29 | POL     |                |                   |                    |
| 10 | Portugal       | PT        |               28 | PRT     |                |                   |                    |

... 33 rows

Open in new window

AuthUserFilter

+-------+--------+------------+---------+--+--+
|  ID   | UserID | FilterType | Include |  |  |
+-------+--------+------------+---------+--+--+
| 11014 |   1206 | U          | 0625    |  |  |
| 11129 |   1242 | U          | 0133    |  |  |
| 13582 |   1326 | U          | 8423    |  |  |
| 18228 |   1718 | U          | test    |  |  |
| 18306 |   2048 | U          | 1003    |  |  |
| 18536 |   2162 | U          | MKIC-FG |  |  |
| 18537 |   2162 | U          | MKIC    |  |  |
| 19308 |   3703 | U          | 29602A  |  |  |
| 19309 |   3703 | U          | 92231A  |  |  |
| 19310 |   3703 | U          | 92618A  |  |  |
+-------+--------+------------+---------+--+--+

masterlease

+----+---------+--------------+--------------+---------------+-----------+------------+-------------+-----------+-----------+-----------+-----------+-----------+-----------+------------+---------------------+---------+---------------------+-------------+--------+---------------+---------------------+------------+------------------+------------------+--------------+
| ID | LeaseNo | AccountRepID | CustomerName | UdFilterLabel | UdfLabel1 | UdfLabel2  |  UdfLabel3  | UdfLabel4 | UdfLabel5 | UdfLabel6 | UdfLabel7 | UdfLabel8 | UdfLabel9 | UdfLabel10 |     WhenUpdated     | Deleted |     DeletedDate     | PortfolioID | Active | International |    LastModified     | ExternalID | ExternalSourceId | ShowArchivedData | KeyCdfColumn |
+----+---------+--------------+--------------+---------------+-----------+------------+-------------+-----------+-----------+-----------+-----------+-----------+-----------+------------+---------------------+---------+---------------------+-------------+--------+---------------+---------------------+------------+------------------+------------------+--------------+
|  1 | 104972  |           82 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | True    | 29/08/2015 00:00:00 |           1 | True   | False         | 22/06/2015 20:15:19 |            |                  | False            |              |
|  2 | 107414  |           81 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | True    | 31/05/2016 00:00:00 |           1 | True   | False         | 22/06/2015 20:15:19 |            |                  | False            |              |
|  3 | 110602A |           79 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | True    | 02/01/2016 00:00:00 |           1 | True   | False         | 22/06/2015 20:15:19 |            |                  | False            |              |
|  4 | 116817  |           72 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | True    | 13/06/2015 00:00:00 |           1 | True   | False         | 22/06/2015 20:15:19 |            |                  | False            |              |
|  5 | 119720  |           82 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | False   |                     |           1 | True   | False         | 22/06/2015 20:15:19 |         14 |                  | False            |              |
|  6 | 130819  |              | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | False   |                     |           1 | True   | False         | 22/06/2015 20:15:19 |         15 |                  | False            |              |
|  7 | 131582  |           81 | test         |               |           |            |             |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | False   |                     |           1 | True   | False         | 22/06/2015 20:15:19 |         17 |                  | False            |              |
|  8 | 131841  |           75 | test         | Budget Code   | Asset Tag | Department | Budget Code |           |           |           |           |           |           |            | 04/02/2013 18:10:41 | False   |                     |           1 | True   | False         | 22/06/2015 20:15:19 |         18 |                  | False            |            3 |
+----+---------+--------------+--------------+---------------+-----------+------------+-------------+-----------+-----------+-----------+-----------+-----------+-----------+------------+---------------------+---------+---------------------+-------------+--------+---------------+---------------------+------------+------------------+------------------+--------------+

Open in new window

schedule

+------+-------+---------------+---------------------+------+--------------+---------------------+----------------------+-----------+----------+------------+------------+-----------+----------+-------------+----------+----------+----------+-------------+--------------+---------+---------------+--------+------------+---------------------+------------+------------------+----------+-----------------+-----------------------+--------+---------------+---------+-------------+--------------------+-------------------+---------+------------+
|  ID  | Sched | MasterLeaseID |  DateLeaseStarted   | Term | TermUnitCode |    DateLeaseEnds    |       Billing        |   Rent    |   Tax    | LesseeCode | LesseeName | Insurance | FreqCode | PayFormCode | CurrCode | Ordering | Location | DateDeleted | AccountRepID | Deleted | International | Status | DeleteDate |    LastModified     | ExternalID | ExternalSourceId | DebtRate | NewHardwareCost | OriginalEquipmentCost | Margin | ResidualValue | FinalPV | InterimRent | VendorInvoiceTotal | ScheduleInsurance | Comment | USGAAPType |
+------+-------+---------------+---------------------+------+--------------+---------------------+----------------------+-----------+----------+------------+------------+-----------+----------+-------------+----------+----------+----------+-------------+--------------+---------+---------------+--------+------------+---------------------+------------+------------------+----------+-----------------+-----------------------+--------+---------------+---------+-------------+--------------------+-------------------+---------+------------+
|    8 |   174 |             5 | 01/06/2011 00:00:00 |   36 |              | 31/05/2014 00:00:00 | MONTHLY IN ADVANCE   | 2982.7100 |   0.0000 | test       | test       |           | M        | AD          | USD      |          |          |             |           82 | False   | False         |      2 |            | 02/12/2017 05:27:18 |            |                  |          |                 |             115845.38 |        |       3689.00 |         |        0.00 |                    |                   |         |            |
|  863 |   065 |           112 | 01/01/2012 00:00:00 |   48 |              | 31/12/2015 00:00:00 | QUARTERLY IN ADVANCE |  499.4400 |  31.2300 | test       | test       |           | Q        | AD          | USD      |          |          |             |           79 | False   | False         |      2 |            | 02/12/2017 05:27:18 |            |                  |          |                 |               8172.88 |        |        351.00 |         |        0.00 |                    |                   |         |            |
|  864 |   066 |           112 | 01/05/2012 00:00:00 |   36 |              | 30/04/2015 00:00:00 | MONTHLY IN ADVANCE   |   81.5500 |   8.1600 | test       | test       |           | M        | AD          | USD      |          |          |             |           79 | False   | False         |      2 |            | 18/10/2018 04:40:32 |            |                  |          |                 |              49342.79 |        |        460.00 |         |        0.00 |                    |                   |         |            |
|  865 |   067 |           112 | 01/04/2012 00:00:00 |   48 |              | 31/03/2016 00:00:00 | QUARTERLY IN ADVANCE |  233.2500 |  14.5700 | test       | test       |           | Q        | AD          | USD      |          |          |             |           79 | False   | False         |      2 |            | 04/12/2018 08:15:10 |            |                  |          |                 |               1638.12 |        |        240.00 |         |        0.00 |                    |                   |         |            |
|  866 |   068 |           112 | 01/07/2012 00:00:00 |   48 |              | 30/06/2016 00:00:00 | QUARTERLY IN ADVANCE | 5401.4800 | 337.5000 | test       | test       |           | Q        | AD          | USD      |          |          |             |           79 | False   | False         |      2 |            | 04/12/2018 08:15:10 |            |                  |          |                 |              52835.08 |        |       4229.00 |         |        0.00 |                    |                   |         |            |
| 1349 |   026 |           182 | 01/01/2012 00:00:00 |   36 |              | 31/12/2014 00:00:00 | MONTHLY IN ADVANCE   |   85.5900 |   7.1200 | test       | test       |           | M        | AD          | USD      |          |          |             |           82 | False   | False         |      2 |            | 04/04/2018 04:08:52 |            |                  |          |                 |               2817.80 |        |        248.00 |         |        0.00 |                    |                   |         |            |
| 2263 |   002 |           412 | 01/08/2011 00:00:00 |   60 |              | 31/07/2016 00:00:00 | MONTHLY IN ADVANCE   |  672.5000 |  87.4500 | test       | test       |           | M        | AD          | CAD      |          |          |             |           87 | False   | False         |      2 |            | 27/01/2018 05:20:32 |            |                  |          |                 |              33527.00 |        |        675.00 |         |        0.00 |                    |                   |         |            |
| 2446 |   023 |            55 | 01/10/2011 00:00:00 |   60 |              | 30/09/2016 00:00:00 | MONTHLY IN ADVANCE   |  726.8700 |  64.6900 | test       | test       |           | M        | AD          | USD      |          |          |             |         3706 | False   | False         |      2 |            | 19/06/2018 03:49:39 |            |                  |          |                 |              49981.85 |        |        656.00 |         |        0.00 |                    |                   |         |            |
+------+-------+---------------+---------------------+------+--------------+---------------------+----------------------+-----------+----------+------------+------------+-----------+----------+-------------+----------+----------+----------+-------------+--------------+---------+---------------+--------+------------+---------------------+------------+------------------+----------+-----------------+-----------------------+--------+---------------+---------+-------------+--------------------+-------------------+---------+------------+

Unit

+---------+------------+----------------+--------------+---------------------------------------------+--------+----------+---------------------+--------------+----------+-------------+--------------+--------------+-----------+---------+-----------------+----------+------------+-------------+--------------+-------------+-----------+---------+-----------------+-----------+-------------+--------------+---------------+-----------+------------+----------+------------------+----------+---------+--------------------------------------------+---------------------+-------------+---------------+------+------+------+------+------+------+------+------+------+-------+---------------+--------------------+-------------+---------+-------------+-------------+----------+---------------+------------+------------+------------+------------+-------------+-------------+------------+----------------------+--------------+---------------------+------------------+----------+-------------+
|   ID    | ScheduleID |    SerialNo    | Manufacturer |                    Model                    | Vendor | Quantity |    DateAccepted     | DateReturned | SiteName | SiteAddress | SiteAddress2 |   SiteCity   | SiteState | SiteZip | SiteCountryCode | BillName | BillToAttn | BillAddress | BillAddress2 |  BillCity   | BillState | BillZip | BillCountryCode | RemitName | RemitToAttn | RemitAddress | RemitAddress2 | RemitCity | RemitState | RemitZip | RemitCountryCode |   Rent   |   Tax   |                 AssetInfo                  |       DateIn        | CustomerRef | UdFilterValue | Udf1 | Udf2 | Udf3 | Udf4 | Udf5 | Udf6 | Udf7 | Udf8 | Udf9 | Udf10 | CSIDataLocked | CustomerDataLocked | InventoryId | Deleted | DateDeleted | Description | Returned | UnitInsurance | SiteLocale | SiteRegion | BillLocale | BillRegion | RemitLocale | RemitRegion | ExternalID | GeographyCoordinates | NonCsiLeased |    LastModified     | ExternalSourceId | Category | DateCreated |
+---------+------------+----------------+--------------+---------------------------------------------+--------+----------+---------------------+--------------+----------+-------------+--------------+--------------+-----------+---------+-----------------+----------+------------+-------------+--------------+-------------+-----------+---------+-----------------+-----------+-------------+--------------+---------------+-----------+------------+----------+------------------+----------+---------+--------------------------------------------+---------------------+-------------+---------------+------+------+------+------+------+------+------+------+------+-------+---------------+--------------------+-------------+---------+-------------+-------------+----------+---------------+------------+------------+------------+------------+-------------+-------------+------------+----------------------+--------------+---------------------+------------------+----------+-------------+
| 7000045 |      35264 | 3CQ6110T8G     | HP           | 23" LED MONITOR                             | test   |        1 | 06/07/2016 00:00:00 |              |          | test        |              | CAMPBELLFORD | ON        | K0L 1L0 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   4.5900 |  0.6000 | (1) Main Box                               | 29/12/2018 00:00:00 |         017 |               |  017 |      |      |      |      |      |      |      |      |       | False         | False              |     2711863 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000046 |      35264 | CN45520HBR     | HP           | 23" LED MONITOR                             | test   |        1 | 06/07/2016 00:00:00 |              |          | test        |              | FREDERICTON  | NB        | E3A 0T3 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   4.3500 |  0.6500 | (1) Main Box                               | 29/12/2018 00:00:00 |         337 |               |  337 |      |      |      |      |      |      |      |      |       | False         | False              |     2711757 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000047 |      35264 | MXL5510L8L     | HP           | ELITEDESK 800 G1 CI5 QC 3.3GHZ              | test   |        1 | 19/05/2016 00:00:00 |              |          | test        |              | NEW GLASGOW  | NS        | B2H 2J6 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |  34.6300 |  5.2000 | (1) Main Box|(1) STARTECH  DESKTOP FEATURE | 29/12/2018 00:00:00 |         067 |               |  067 |      |      |      |      |      |      |      |      |       | False         | False              |     2672341 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000048 |      35264 | CN46111DD5     | HP           | 23" LED MONITOR                             | test   |        1 | 06/07/2016 00:00:00 |              |          | test        |              | Gloucester   | ON        | K1T 0K8 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   4.5900 |  0.6000 | (1) Main Box                               | 29/12/2018 00:00:00 |         659 |               |  659 |      |      |      |      |      |      |      |      |       | False         | False              |     2711780 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000049 |      35264 | 17501102909154 | DYMO         | LABEL WRITER 450 THERMAL PRINTER            | test   |        1 | 06/07/2016 00:00:00 |              |          | test        |              | CHELMSFORD   | ON        | P0M 1L0 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   2.0300 |  0.2600 | (1) Main Box                               | 29/12/2018 00:00:00 |         244 |               |  244 |      |      |      |      |      |      |      |      |       | False         | False              |     2711465 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000066 |      35264 | 061621163      | HERO         | A260-12E ARCHIMEDE COLORANT DISPENSE SYSTEM | test   |        1 | 22/06/2016 00:00:00 |              |          | test        |              | ROTHESAY     | NB        | E2E 2R3 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  | 216.4600 | 32.4700 | (1) Main Box                               | 29/12/2018 00:00:00 |         476 |               |  476 |      |      |      |      |      |      |      |      |       | False         | False              |     2631952 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000068 |      35264 | 17501102909151 | DYMO         | LABEL WRITER 450 THERMAL PRINTER            | test   |        1 | 06/07/2016 00:00:00 |              |          | test        |              | COBOURG      | ON        | K9A 5T9 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   2.0300 |  0.2600 | (1) Main Box                               | 29/12/2018 00:00:00 |         023 |               |  023 |      |      |      |      |      |      |      |      |       | False         | False              |     2711500 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000070 |      35264 | 061609634      | HERO         | A460-12E EUREKA COLORANT DISPENSE SYSTEM    | test   |        1 | 21/07/2016 00:00:00 |              |          | test        |              | Hamilton     | ON        | L8H 2V4 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  | 356.3000 | 46.3200 | (1) Main Box                               | 29/12/2018 00:00:00 |         129 |               |  129 |      |      |      |      |      |      |      |      |       | False         | False              |     2691571 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 7000077 |      35264 | 17501102980651 | DYMO         | LABEL WRITER 450 THERMAL PRINTER            | test   |        1 | 30/11/2016 00:00:00 |              |          | test        |              | PENTICTON    | BC        | V2A 8N2 | CAN             | test     | test       | test        |              | Mississauga | ON        | L5T 2J1 |                 |           |             |              |               |           |            |          |                  |   1.8700 |  0.2200 | (1) Main Box                               | 29/12/2018 00:00:00 |         351 |               |  351 |      |      |      |      |      |      |      |      |       | False         | False              |     2846756 | False   |             |             |          |               |            |            |            |            |             |             |            |                      | False        | 29/12/2018 08:33:51 |                  |          |             |
| 9 rows  |            |                |              |                                             |        |          |                     |              |          |             |              |              |           |         |                 |          |            |             |              |             |           |         |                 |           |             |              |               |           |            |          |                  |          |         |                                            |                     |             |               |      |      |      |      |      |      |      |      |      |       |               |                    |             |         |             |             |          |               |            |            |            |            |             |             |            |                      |              |                     |                  |          |             |
+---------+------------+----------------+--------------+---------------------------------------------+--------+----------+---------------------+--------------+----------+-------------+--------------+--------------+-----------+---------+-----------------+----------+------------+-------------+--------------+-------------+-----------+---------+-----------------+-----------+-------------+--------------+---------------+-----------+------------+----------+------------------+----------+---------+--------------------------------------------+---------------------+-------------+---------------+------+------+------+------+------+------+------+------+------+-------+---------------+--------------------+-------------+---------+-------------+-------------+----------+---------------+------------+------------+------------+------------+-------------+-------------+------------+----------------------+--------------+---------------------+------------------+----------+-------------+

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
I think what I am really failing to understand is the "context" of these security queries.

If I am user  and I want to access a portfolio, then to present a list of portfolios for that user to choose from could look like this:
declare @userid integer = 88

select p.*
from Portfolio p
where (
   0 = (select FilterPortfolios from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
   OR
   p.id in (select PortfolioId from AuthUserPortfolio where AuthUserId = @Userid) /* check by portfolio level */
   )
;


declare @userid integer = 14307

select ml.*
from masterlease ml
where (
   0 = (select FilterPortfolios from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
   OR
   ml.id in (select PortfolioId from AuthUserPortfolio where AuthUserId = @Userid) /* check by portfolio level */
   )
 ;

Open in new window

So, in this "context" (wanting a list of portfolios) I don't need to use any other tables than [Portfolio ] and [AuthUser ] and [AuthUserPortfolio] and I don't need to test for leases ot units or schedules etc.,

And, you could do similar approach for MasterLeases, e.g.
declare @userid integer = 88

select ml.*
from masterlease ml
where (
   0 = (select FilterMasterLeases from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
   OR
   ml.id in (select MasterLeaseId from AuthUserMasterLease where AuthUserId = @Userid) /* check by object instance level */
   )
 ;

declare @userid integer = 14307

select ml.*
from masterlease ml
where (
   0 = (select FilterMasterLeases from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
   OR
   ml.id in (select MasterLeaseId from AuthUserMasterLease where AuthUserId = @Userid) /* check by object instance level */
   )
;

Open in new window


I guess I also really don't get the point of attempting to return a very "wide" set of columns like this:
SELECT 

             cst.ScheduleID,
		    cst.ID AS UnitID,
             SerialNo,
		    Manufacturer,
		    Model,
		    Vendor,
		    Quantity,
		    DateAccepted,
		    DateReturned,
			--SiteName, --EF doesn't bring back this 
		    SiteAddress,
		    SiteAddress2,
		    SiteCity,
		    SiteState,
		    SiteZip,
		    SiteCountryCode,
		    BillName,
		    BillToAttn,
		    BillAddress,
		    BillAddress2,
		    BillCity,
		    BillState,
		    BillZip,
		    BillCountryCode,
		    RemitName,
		    RemitToAttn,
		    RemitAddress,
		    RemitAddress2,
		    RemitCity,
		    RemitState,
		    RemitZip,
		    RemitCountryCode,
		    Rent,
		    Tax,
		    AssetInfo,
		    DateIn,
		    CustomerRef,
		    UdFilterValue,
		    Udf1,
		    Udf2,
		    Udf3,
		    Udf4,
		    Udf5,
		    Udf6,
		    Udf7,
		    Udf8,
		    Udf9,
		    Udf10,
		    CSIDataLocked,
		    CustomerDataLocked,
		    InventoryId,
		    Deleted,
		    DateDeleted,
		    Description,
		    Returned,
		    UnitInsurance,
		    SiteLocale,
		    SiteRegion,
		    BillLocale,
		    BillRegion,
		    RemitLocale,
		    RemitRegion,
		    ExternalID,
		    GeographyCoordinates,
		    NonCsiLeased,
		    LastModified,
		    ExternalSourceId,
		    Category,
		    DateCreated,
		    ID1,
		    Sched,
		    cst.MasterLeaseID,
		    DateLeaseStarted,
		    Term,
		    TermUnitCode,
		    DateLeaseEnds,
		    Billing,
		    Rent1,
		    Tax1,
		    LesseeCode,
		    LesseeName,
		    Insurance,
		    FreqCode,
		    PayFormCode,
		    CurrCode,
		    Ordering,
		    Location,
		    DateDeleted1,
		    AccountRepID,
		    Deleted1,
		    International,
		    Status,
		    DeleteDate,
		    LastModified1,
		    ExternalID1,
		    ExternalSourceId1,
		    DebtRate,
		    NewHardwareCost,
		    OriginalEquipmentCost,
		    Margin,
		    ResidualValue,
		    FinalPV,
		    InterimRent,
		    VendorInvoiceTotal,
		    ScheduleInsurance,
		    Comment,
		    USGAAPType,
		    ID2,
		    LeaseNo,
		    AccountRepID1,
		    CustomerName,
		    UdFilterLabel,
		    UdfLabel1,
		    UdfLabel2,
		    UdfLabel3,
		    UdfLabel4,
		    UdfLabel5,
		    UdfLabel6,
		    UdfLabel7,
		    UdfLabel8,
		    UdfLabel9,
		    UdfLabel10,
		    WhenUpdated,
		    Deleted2,
		    DeletedDate,
		    cst.PortfolioID,
		    Active,
		    International1,
		    LastModified2,
		    ExternalID2,
		    ExternalSourceId2,
		    ShowArchivedData,
			p.ShortName 

Open in new window

because only some of those columns belong to each of the different object types.

Something like the following might prove more efficient and easier to comprehend
declare @userid integer = 88

select 'portfolio' as type, p.id pkid, p.name reference
from Portfolio p
where (
   0 = (select FilterPortfolios from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
   OR
   p.id in (select PortfolioId from AuthUserPortfolio where AuthUserId = @Userid) /* check by object instance level */
   )

UNION ALL

    select 'masterlease', ml.id, ml.LeaseNo
    from masterlease ml
    where (
       0 = (select FilterMasterLeases from AuthUser where id = @Userid) /* chack at user level for access to all portfolios */
       OR
       ml.id in (select MasterLeaseId from AuthUserMasterLease where AuthUserId = @Userid) /* check by object instance level */
       )
 

Open in new window

and keep following this approach for each security test object by object - in any order you choose.
Now because the columns of each object's table are very different you would need to be choose which ones include in this unioned result but I suggest it does NOT need to be every column of every object.

I do hope this is of some use, but I may have misunderstood the essential purpose of your stored procedure, Please forgive me if this just adds to any lingering confusion.

latest db<>fiddle here
CamilliaAuthor Commented:
Let me take a look. Thanks for taking the time and doing this.
CamilliaAuthor Commented:
I guess I also really don't get the point of attempting to return a very "wide" set of columns like this:

The stored proc binds to a grid. That's why I have those columns.

---------------
Let me try your SQL. I wonder if I should just do a bunch of if-else statements (ex: if user has Filter column, join and get rows that user has access to. If user doesn't have any filters....just get all the rows) and put myself out of my misery and get my moody manager off my back. But I know it can be done with "left join" to those AuthUserxxx tables and looking at those Filter columns (like the stored proc that my manager doesn't want now)

But let me try what you have and I'll post back.

 ( side note: talking with HR this week as someone suggested here in my other thread)
slightwv (䄆 Netminder) Commented:
Remember that we really don't have a clear picture of how everything plugs together so this might be WAY OFF.

The below query doesn't include the portfolios because I don't know how those join in to the other tables and if I comment it back in, I think it returns many more rows that you want.

The authuserfilter table doesn't seem to make much sense compared to the others.  You set filterschedules on Edward so he cannot see all of them but in authfilteruser, you have scheduleids that Edward can now see because he isn't filtered on those?

Seems like an authUserUnit table would match the other designed tables.

Since you never posted the expected results from all that data, I have no way to confirm if I'm right or not.

Here is what I came up with.  Hopefully you can see where I was going and can fill in the gaps that I missed.

If not, please post where it isn't quite right and the expected results so I can know when I'm right before posting.
select
	count(*)
from
	authuser a
		--units
			left join authuserfilter auf on a.id=auf.userid and filtertype='U'
			join unit u on u.id=auf.include or a.filterudfs=0
		--schedules
			left join authuserschedule aus on a.id=aus.authuserid
			join schedule s on (s.id=aus.scheduleid or a.filterschedules=0) and s.id=u.scheduleid
		--masterleases
			left join authusermasterlease aml on a.id=aml.authuserid
			join masterlease ml on (ml.id=aml.masterleaseid or a.filtermasterleases=0) and ml.id=s.masterleaseid
		--portfolios
			--left join authuserportfolio aup on a.id=aup.authuserid
			--join portfolio p on p.id=aup.portfolioid or a.filterportfolios=0
--where a.id=88
where a.id=14307
;

Open in new window

CamilliaAuthor Commented:
let me take a look. Let me run a test on what you and Paul have.

Thanks for sticking with this. I really appreciate helping me out. If all fails, I'll go with if-else and post here what I'm thinking.
CamilliaAuthor Commented:
OMG! I think I won't get yelled at in the morning by my manager :) Looks like both your SQLs will work...

Paul's looks more efficiant and what my manager might approve.

Slight's works too, a bit slower, but it's all in one place. I have to add some filtering like status <> 1 and stuff and this will be useful as well.

I'll test more in the morning when I go to work. I can't think you both enough. I'll post back.
slightwv (䄆 Netminder) Commented:
You need to keep then end game in mind.  Since you mention populating a grid with many of the values, there is a difference between the approaches.

With mine you can select ALL the columns from ALL the tables on a single row.

With a union approach you only get the individual values from each union.  If you need them on the same row then you would need to PIVOT them.

For example, you want
      MasterLease.CustomerName and Schedule.LesseeName  on the same row.

For mine, it is simple, just add them:
select
	ml.CustomerName,
	s.LesseeName 
from
	authuser a
		--units
			left join authuserfilter auf on a.id=auf.userid and filtertype='U'
			join unit u on u.id=auf.include or a.filterudfs=0
		--schedules
			left join authuserschedule aus on a.id=aus.authuserid
			join schedule s on (s.id=aus.scheduleid or a.filterschedules=0) and s.id=u.scheduleid
		--masterleases
			left join authusermasterlease aml on a.id=aml.authuserid
			join masterlease ml on (ml.id=aml.masterleaseid or a.filtermasterleases=0) and ml.id=s.masterleaseid
		--portfolios
			--left join authuserportfolio aup on a.id=aup.authuserid
			--join portfolio p on p.id=aup.portfolioid or a.filterportfolios=0
--where a.id=88
where a.id=14307
;

Open in new window


If you can change the user interface to pick and choose what you want, then Paul's choice might be better.  No need to select from a table you don't need to use.

Mine might also perform better with a few well placed indexes.
CamilliaAuthor Commented:
Yes, I'll play around with both when I go to work tomorrow.
PortletPaulEE Topic AdvisorCommented:
You need to keep then end game in mind.

Absolutely. It is that "end game" I have no appreciation for.
CamilliaAuthor Commented:
Absolutely. It is that "end game" I have no appreciation for.

What do you mean, Paul?
CamilliaAuthor Commented:
Been testing. I'll post back a question I have about Slight's query.  

Thanks to both of you again for helping me out.
slightwv (䄆 Netminder) Commented:
>>What do you mean, Paul?

I'll try to guess at what Paul meant from my perspective:  We still aren't sure what the final results will be used for and how the system behaves.  You mentioned populating a grid but with what data?  How will it be presented?  How do the users interact with the front end to get the results?

If the users have some GUI that allows them to limit the results, how is it designed?  Can it be modified?

Can the users select ONLY PORTFOLIOS?  If so, then a dropdown populated by a separate query to get available options before the final query is probably the correct way to go.  Then there may be no need to include the join to schedule table in the final query.

No need for a single query to get everything if everything isn't necessary.
CamilliaAuthor Commented:
ah, ok. Thanks. Let me test what you guys gave me and I'll see how it works.

This application was designed four years ago and I'm relatively new at this job. I think it could've been designed and coded better. We're running into performance issues and that's why I'm working on this stored proc to speed up the grid.

You have valid points, for me to think about after I test this stored proc. I'll post back. Thanks again.
PortletPaulEE Topic AdvisorCommented:
Slightwv, thanks, an excellent summary.

Can the users select ONLY PORTFOLIOS?  If so, then a dropdown populated by a separate query to get available options before the final query is probably the correct way to go.

Camilla I  said with my original suggestion that I  do not know "the context" for your query and this remains true. E.g. I do not know why you need so many columns returned.

So I  suggested an approach which might, or might not, meet your needs. Hopefully my suggestion helps you find the best approach.
CamilliaAuthor Commented:
Yes. You guys have been great. I'll test more with different users and I know I can adjust them to meet what I need, if it needs any changes.
CamilliaAuthor Commented:
I'll keep this question open for a couple of more days just in case I have a question (waiting for my manager to review).FYI
slightwv (䄆 Netminder) Commented:
No problem.  We're here and waiting to see how things work out!
PortletPaulEE Topic AdvisorCommented:
Echo: no problem ...
CamilliaAuthor Commented:
Thank you both. Both methods work and my manager (after he yelled yesterday) made some changes and he gave me the final version. Thanks again. I really apperciate the help from you two. I know you have other things to do but still stuck with this question.

What would I do without you guys and EE :)
slightwv (䄆 Netminder) Commented:
Sorry to hear that it doesn't sound like this issue was solved.

We're here when you need help again!
CamilliaAuthor Commented:
No it was solved. My manager changed it around because he's more familiar with the logic and it works now. He yelled anyway :)
PortletPaulEE Topic AdvisorCommented:
Would  it be posible to see the solution? It would be useful to understand what was needed.
CamilliaAuthor Commented:
Yes. I'll post it when I get back to work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.