asked on

(I have a solution) Inner Join but I also need all the rows

I don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables

CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

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

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window

2. Populate tables

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

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

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window

4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window

My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end

Open in new window

Kyle Abrahams, PMP

you can accomplish this in one query:

 declare @userid int = 88

 select * from 
-- start with users
 #Authuser  au

-- bring in schedules they have access to 
 left join #AuthuserSched  aus on au.id = aus.id

-- get the schedules for those they have access to or all of them if filter is 0
 left join  #main  m on aus.SchedID = m.SchedID or au.FilterSched = 0

-- limit by the user in question.
  where 
 au.id = @userid

Open in new window

Camillia

ASKER

Let me see Kyle. Thanks for responding.

Scott Pletcher

Another alternative. You can use LEFT OUTER JOIN if you prefer.

SELECT M.*
FROM #Authuser Au
INNER JOIN #Main M ON Au.FilterSched = 0 /*all*/ OR
EXISTS(SELECT 1 FROM #AuthuserSched AuS WHERE AuS.SchedId = M.SchedId /*Edit*/ and AuS.UserID = Au.ID)
WHERE Au.ID = @userid

Camillia

ASKER

I'll try both. I'll post back

Kyle Abrahams, PMP

Scott,

Just cause I like to learn mind addressing performance in your query versus mine?

also I believe you would need to add the userid:

   EXISTS(SELECT 1 FROM #AuthuserSched AuS WHERE AuS.SchedId = M.SchedId and AUs.UserID = @userID)

Open in new window

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

I have a problem. I have to check #AuthUser to see if FilterSched is set to 0 or 1. I can't just assume I can go to #AuthUserSched table.

In Kyle's SQL, this does it?

left join  #main  m on aus.SchedID = m.SchedID or au.FilterSched = 0

Open in new window

In Scott's version, this doesn't do it?

   EXISTS(SELECT 1 FROM #AuthuserSched AuS WHERE AuS.SchedId = M.SchedId /*Edit*/ and AuS.UserID = Au.ID)

Open in new window

And can I not go to #Authuser? In my stored proc (actual SQL I have), I'm not using #AuthUser. This is the actual SQL, if it helps. Look at the last select. I have comments in there. I don't know how to apply the solution to what I have below. I can upload sample of actual data, if needed. If not doable with what I have below, then I'll just use my solution.... I don't like it tho. I know it can be done better.

#main corresponds to dbo.BigAssetSearch

I'll see how I can apply the solutions to the last Select I have. If I figure it out, I'll post back.

/****** 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_SearchAssets]
@searchtext nvarchar(4000),
@userId INT
As
BEGIN

-- exec [dbo].[usp_SearchAssets] '%019%'


DECLARE @FilterSchedules BIT,
@FilterPortfolios BIT,
@FilterMasterLeases BIT,
@FilterUDFs bit

--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 = '%O19%' --**************** remove *******************************************

 --account for 0 and O that code has
--DECLARE @results TABLE(SearchText NVARCHAR(4000))

--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
 )

 -- temp tables for filters
 CREATE TABLE #FilterScheds
 (
   SchedId int
 )


  CREATE TABLE #FilterPorfolios
 (
   PortfolioId int
 )

 CREATE TABLE #FilterMasterLeases
 (
   MasterId int
 )

 CREATE TABLE #FilterUDFs
 (
   UDFId int
 )


 -- end if temp tables for filters

 -- get user permissions
SELECT @FilterPortfolios = FilterPortfolios,
       @FilterSchedules = FilterSchedules,
       @FilterMasterLeases = FilterMasterLeases,
       @FilterUDFs = FilterUDFs
FROM dbo.AuthUser
WHERE ID = @userId;

 --*** unit search
INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
  rowid

FROM

	 dbo.BigAssetSearch AS u

WHERE 

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )

--*** filter section
--check for sched filter
IF @FilterSchedules = 1 -- get list of schedules user has access to
 BEGIN
   
   --SELECT * FROM dbo.AuthUserSchedule WHERE AuthUserId = @userid

   INSERT INTO #FilterScheds
   (
       SchedId
   )
    SELECT ScheduleId FROM dbo.AuthUserSchedule WHERE AuthUserId = @userid

 END 

 --check for portfolio filter
 IF @FilterPortfolios = 1 -- get list of schedules user has access to
 BEGIN
   
  INSERT INTO #FilterPorfolios
   (
       PortfolioId
   )
 
    SELECT [PortfolioId] FROM [dbo].[AuthUserPortfolio] WHERE AuthUserId = @userid

 END 

  --check for master lease filter
 IF @FilterPortfolios = 1 -- get list of schedules user has access to
 BEGIN
   
    INSERT INTO #FilterMasterLeases
    (
        MasterId
    )
    
    SELECT [MasterLeaseId]  FROM [dbo].[AuthUserMasterLease] WHERE AuthUserId = @userid

 END 

 --*** end of filter section 



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
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      )

	-- OR  s.ScheduleID IN (SELECT * FROM #FilterScheds) -- filter by what the user has access to


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 )



SELECT 

            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,
		    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

			--INTO camillasptest

FROM dbo.BigAssetSearch cst
--LEFT JOIN dbo.AuthUserSchedule aus ON 
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
inner JOIN #FilterScheds f ON f.SchedId = cst.ScheduleID -- try this instead of IN below ****** what about when user has access to everything? should I populate the temp table with all schedIDs?
INNER JOIN #FilterPorfolios pf ON pf.PortfolioId = cst.PortfolioID --try this instead of IN below ****** what about when user has access to everything? should I populate the temp table with all schedIDs?
INNER JOIN	#FilterMasterLeases ml ON ml.MasterId = cst.MasterLeaseID --try this instead of IN below ****** what about when user has access to everything? should I populate the temp table with all schedIDs?
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	
	-- and cst.ScheduleID IN (SELECT * FROM #FilterScheds) --try it here. 



 drop table #unitsearch
 drop table #schedsearch
 DROP TABLE #MLSearch
 DROP TABLE #FilterScheds
 DROP TABLE #FilterMasterLeases
 DROP TABLE #FilterPorfolios
 DROP TABLE #FilterUDFs

 End

Open in new window

Camillia

ASKER

I did this and still didn't bring back rows for 14307. Brought back rows for 88 userid

.....
FROM dbo.BigAssetSearch cst
INNER JOIN dbo.AuthUser au ON au.FilterSchedules = 0 OR  EXISTS(SELECT 1 FROM dbo.AuthUserSchedule AuS WHERE AuS.ScheduleId = cst.ScheduleID /*Edit*/ and AuS.AuthUserId = Au.ID)

Open in new window

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

INNER JOIN dbo.AuthUser au ON au.FilterSchedules = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserSchedule AuS WHERE AuS.ScheduleId = cst.ScheduleID /*Edit*/ and AuS.AuthUserId = @userID)

Camillia

ASKER

ok, thanks, Kyle. I understand what you're saying. Let me try it (my moody manager is on my case so I'm kinda getting frustrated but I'll work thru this on the weekend, if I have to. So, thanks for sticking with this)

I'll post back.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

This one didn't work. I had to stop the query. Let me try Kyle's solution. I can upload the data but let me see how Kyle's solution works first.

FROM dbo.BigAssetSearch cst
INNER JOIN dbo.AuthUser au ON au.FilterSchedules = 0 OR  EXISTS(SELECT 1 FROM dbo.AuthUserSchedule AuS WHERE AuS.ScheduleId = cst.ScheduleID /*Edit*/ and AuS.AuthUserId = @userID)
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID

Open in new window

Camillia

ASKER

And yes, let me change it to start from AuthUser.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

I'll post back , Scott. If I have to upload data, I'll do that. But let me step back.... getting frustrated with my manager and I think to think straight.

Let me try what you guys have and I'll post back.

Scott Pletcher

No problem, I'm kinda grumblely today too.

Either query approach should work, I think.

Camillia

ASKER

Yes! looks like it's going to work. I need to do the same for the other 2 temp tables I have (remove them and use the actual table, same concept as you guys have). I'll try and test and post back. This is great :)

Camillia

ASKER

Back at this guy, to wrap it up. I need to do this for 2 more tables. Very close to getting this done...

Now, I need to use the AuthPortfolios. For user with data in AuthPortfolio, it doubles the rows. For user with no data in authporfolio, I get correct number of rows. I think it's because of user 88, I have 2 rows in AuthPorfolio...one for Portfolio 1, one for Portfilio 2. For user 14307, I don't have any rows in AUthPorfolio, so I get correct rows.

(I can PM you guys a link to the data, if needed)

If I figure it out, I'll postback

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id -- *** this brings back dups? I have 2 rows for user 88...one for Porfolio 1 and one for Portfilio 2
left join dbo.BigAssetSearch cst on cst.ScheduleID = aus.ScheduleId or  au.FilterSchedules = 0 OR au.FilterPortfolios = 0
inner JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID --- *** I need this because dbo.Porfolio has the "Name" column that I need.

Open in new window

EDIT: but I have more than one rows for user 88 in AuthUserScheudles and that didn't bring extra rows..I got correct rows for that. Has to be something with my join?

Camillia

ASKER

Changed it to this but now the join is going on and on..... I've noted it below to what I changed it to. This is to fix the dups I'm getting for user 88

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
--**** here....I added --> OR (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
left join dbo.BigAssetSearch cst on (cst.ScheduleID = aus.ScheduleId or  au.FilterSchedules = 0) OR (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
inner JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID

Open in new window

Did this as well but got 47 million rows :) I should only get 2211 rows for user 88

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
left join dbo.BigAssetSearch cst on cst.ScheduleID = aus.ScheduleId or  au.FilterSchedules = 0-- OR (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
LEFT JOIN dbo.BigAssetSearch cst2 ON  au.FilterPortfolios = 0 OR cst2.PortfolioID = aup.PortfolioId
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID

Open in new window

Camillia

ASKER

I think I got it. Getting correct number of rows I did this (this join)
left join dbo.BigAssetSearch cst on (cst.ScheduleID = aus.ScheduleId or au.FilterSchedules = 0) and (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )

I have to add one more table. I'll post back if I need help with that one.

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
left join dbo.BigAssetSearch cst on (cst.ScheduleID = aus.ScheduleId or  au.FilterSchedules = 0) and (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )

Open in new window

Camillia

ASKER

My final one Yes, it works. I thought I have an issue but no ... that was data. I need to add one more filter but I have to look at the C# code.

Thank you both for helping me out on this.

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
									 AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE

Open in new window

Camillia

ASKER

I have another question. I'll post it here (if I need to open another question, please let me know)

How can I do this? I have another table that I need to look at. This is the logic

1. AuthUserFilter looks like this

2. In #AuthUser, I have this. FilterUDF is new. Can be true or false

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched,
    FilterUDF
)
SELECT 88, 'Edward', 1  ,1

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched,
    FilterUDF ---*** this is new
)
SELECT 14307, 'Camillia', 0 ,0

Open in new window

3. This is the C# code I need to translate to SQL. I think I can do it with If-else but wondering if I can add it to the join

If FilterUDF flag for user is 1 and if AuthUserFilter table has rows for the user -->count >0, then bring rows by bigassetsearch.udffiltervalue = AUthUserFilter.Include.

If FilterUDF flag for user is 1 and AuthUserFilter table does NOT rows for the user --> count =0, stored proc shouldn't return any rows. Just ignore all the joins. No data.

How can I do this? I've noted it below. I think I can do this with an if else before I get down to this where clause... but don't think it's a good solution.

FROM 
dbo.AuthUser au
left join  AuthUserSchedule aus ON aus.AuthUserId = au.ID
LEFT JOIN dbo.AuthUserPortfolio aup ON aup.AuthUserId = au.id
LEFT JOIN	dbo.AuthUserMasterLease auml ON auml.AuthUserId = au.ID
LEFT JOIN	dbo.AuthUserUDF audf ON audf.UserId = au.ID --**** here is the join 
left join dbo.BigAssetSearch cst on (au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId ) 
                                     AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
				     AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId
                                     AND (au.FilterUDF = 0 OR ***--> here how do I say if count AuthUserUDF>0  for this user and if so, audf.include=cst.usffiltervalue -->otherwise, don't bring any rows back)
)
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE (EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid))
	 AND 	Au.ID = @userid --*** from EE

Open in new window

Scott Pletcher

I consider it the same core q.

I think a combination of the following changes will do it. Since it's only a left join on cst, some of the conditions must go in the WHERE clause:

...
left join dbo.BigAssetSearch cst on
(au.FilterSchedules = 0 OR cst.ScheduleID = aus.ScheduleId )
AND (au.FilterPortfolios = 0 OR cst.PortfolioID = aup.PortfolioId )
AND (au.FilterMasterLeases = 0 OR cst.MasterLeaseID = auml.MasterLeaseId)
AND (au.FilterUDF = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID AND audf.include=cst.usffiltervalue))
...
WHERE ...
AND Au.ID = @userid --*** from EE
AND (au.FilterUDF = 0 OR EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID))

This coding in the left join is a bit lengthier but is clearer about the exact conditions being checked:

AND (au.FilterUDF = 0 OR (au.FilterUDF = 1 AND EXISTS(SELECT 1 FROM dbo.AuthUserFilter auf WHERE auf.UserId = Au.ID)))

Camillia

ASKER

Thanks, Scott. Let me see. I'll post back.

Camillia

ASKER

I think it works. Let me test it more. If I have any issues, I'll post back.