I'm using LLBL 1.x (for this project) with SQL Server 2000. I'm trying to figure out how I can do something that I know how to do two different ways via SQL, using a FetchTypedList. I've pasted both versions of the SQL that works below. Can anyone advise on how to do this via LLBL?

In short, I need to get back a list of ID's based on some filtering criteria and sorting criteria, but part of the criteria that I need to filter on is against a table that also has data in it that might be excluded by the filter, but that I need anyway: and that data that I need has to be filtered in a way that returns one record for each ID that the outermost part of the query is returning, so I can sort on it.

For example, I need back:

i.ID | t1.DateCreated

and that will be sorted on t1.DateCreated.

Using derived table:

exec sp_executesql N'
SELECT DISTINCT 
[i].[MailboxItemID] AS [MailboxItemID]
 ,[Proposal].[Description] AS [ProposalName]
 ,t1.DateCreated
FROM 
[MailboxItem] [i]
INNER JOIN [MailboxItemMailboxItemStatusMap] 
 ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
INNER JOIN [ProposalMailboxItemMap] 
 ON  [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
INNER JOIN [Proposal] 
 ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
LEFT JOIN 
(
    select map.mailboxItemID, map.DateCreated
    from mailboxItemMailboxItemStatusMap map 
    where map.mailboxItemStatusID in (6,7)
) as t1 on t1.mailboxItemID = [i].mailboxItemID
WHERE 
( 
[i].[MailboxID] = @MailboxID1 
 And [i].[CommitDate] IS NOT NULL 
 And [i].[IsDeleted] = @IsDeleted2 
 And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN ( @MailboxItemStatusID3, @MailboxItemStatusID4 ) 
 And NOT [i].[MailboxItemID] IN 
 (
  SELECT [MailboxItemMailboxItemStatusMap].[MailboxItemID] AS [MailboxItemID] 
  FROM 
  ( 
   [MailboxItem] 
   INNER JOIN [MailboxItemMailboxItemStatusMap] 
    ON [MailboxItem].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
  ) 
  WHERE ( [MailboxItem].[MailboxID] = @MailboxID5 
  And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN (@MailboxItemStatusID6)
 )
)
) 
ORDER BY [Proposal].[Description] 
ASC',N'@MailboxID1 int,@IsDeleted2 bit,@MailboxItemStatusID3 smallint,@MailboxItemStatusID4 smallint,@MailboxID5 int,@MailboxItemStatusID6 
smallint',@MailboxID1=112,@IsDeleted2=0,@MailboxItemStatusID3=2,@MailboxItemStatusID4=5, @MailboxID5=112,@MailboxItemStatusID6=10

Using subquery in the SELECT:

exec sp_executesql N'
SELECT DISTINCT 
[i].[MailboxItemID] AS [MailboxItemID]
 ,[Proposal].[Description] AS [ProposalName]
 , (
    select map.DateCreated
    from mailboxItemMailboxItemStatusMap map 
    where map.MailboxItemID = [i].[MailboxItemID]
    and map.mailboxItemStatusID in (6,7)
   ) as declineDate
FROM 
(
 (
  ( 
   [MailboxItem] [i]
   INNER JOIN [MailboxItemMailboxItemStatusMap] 
    ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
  ) 
  INNER JOIN [ProposalMailboxItemMap] 
   ON  [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
 ) 
 INNER JOIN [Proposal] 
  ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
) 
WHERE 
( 
[i].[MailboxID] = @MailboxID1 
 And [i].[CommitDate] IS NOT NULL 
 And [i].[IsDeleted] = @IsDeleted2 
 And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN ( @MailboxItemStatusID3, @MailboxItemStatusID4 ) 
 And NOT [i].[MailboxItemID] IN 
 (
  SELECT [MailboxItemMailboxItemStatusMap].[MailboxItemID] AS [MailboxItemID] 
  FROM 
  ( 
   [MailboxItem] 
   INNER JOIN [MailboxItemMailboxItemStatusMap] 
    ON [MailboxItem].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
  ) 
  WHERE ( [MailboxItem].[MailboxID] = @MailboxID5 
  And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN (@MailboxItemStatusID6)
 )
)
) 
ORDER BY [Proposal].[Description] 
ASC',N'@MailboxID1 int,@IsDeleted2 bit,@MailboxItemStatusID3 smallint,@MailboxItemStatusID4 smallint,@MailboxID5 int,@MailboxItemStatusID6 
smallint',@MailboxID1=112,@IsDeleted2=0,@MailboxItemStatusID3=2,@MailboxItemStatusID4=5, @MailboxID5=112,@MailboxItemStatusID6=10

The key tables are:

MailboxItem

• MailboxItemID

MailboxItemMailboxItemStatusMap - contains cols:

• MailboxItemID | MailboxItemStatusID | DateCreated

and each MailboxItemMailboxItemStatusMap row contains a status for the MailboxItemID: a MailboxItem can have up to 16 different statuses, one row for each status. Hopefully the structure of the queries above will be enough for someone to understand what I'm trying to do.

Thanks!

• Jack

Could the following part:

SELECT DISTINCT 
[i].[MailboxItemID] AS [MailboxItemID]
,[Proposal].[Description] AS [ProposalName]
,t1.DateCreated
FROM 
[MailboxItem] [i]
INNER JOIN [MailboxItemMailboxItemStatusMap] 
ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
INNER JOIN [ProposalMailboxItemMap] 
ON [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
INNER JOIN [Proposal] 
ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
LEFT JOIN 
(
    select map.mailboxItemID, map.DateCreated
    from mailboxItemMailboxItemStatusMap map 
    where map.mailboxItemStatusID in (6,7)
) as t1 on t1.mailboxItemID = [i].mailboxItemID

Be changed to:

SELECT DISTINCT 
[i].[MailboxItemID] AS [MailboxItemID]
,[Proposal].[Description] AS [ProposalName]
,t1.DateCreated
FROM 
[MailboxItem] [i]
INNER JOIN [MailboxItemMailboxItemStatusMap] 
ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
INNER JOIN [ProposalMailboxItemMap] 
ON [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
INNER JOIN [Proposal] 
ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
LEFT JOIN [mailboxItemMailboxItemStatusMap] t1
ON t1.mailboxItemID = [i].mailboxItemID
WHERE
t1.mailboxItemStatusID in (6,7)
-- rest of the where predicates

I tried that, too. It does not work, because of the way the same table is joined to more than once. You wrote:

...
INNER JOIN [MailboxItemMailboxItemStatusMap]
ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
INNER JOIN [ProposalMailboxItemMap]
ON [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
INNER JOIN [Proposal]
ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
LEFT JOIN [mailboxItemMailboxItemStatusMap] t1
ON t1.mailboxItemID = [i].mailboxItemID
...

Notice that MailboxItemMailboxItemStatusMap gets joined to twice there. Also notice that in the WHERE clause there are various clauses that also filter on MailboxItemMailboxItemStatusMap. Basically the table has to be filtered on to get the outermost recordset (the list of distinct ID's that I need), but also has to return the DateCreated from MailboxItemMailboxItemStatusMap via a different filter on that table.

When I try this, instead of getting all the records I need from the MailboxItem table, I get only items where DateCreated is not null, despite using the LEFT JOIN.

This does seem "close", though.... :-)

Another way to maybe think of this is as if I need DateCreated to be an aggregate (like min) based on filtering criteria that differs from the filter against the same table used by the outermost part of the query. In this case, I don't actually need an aggregate for DateCreated, since there will always only be one record, but I could use an aggregate if that made this more feasible via LLBL.... IE, min(DateCreated) would return the same as simple DateCreated, since there will always be only one record.

If it helps give context to this....this is part of a paging/sorting process where paging/sorting params are passed into a method and the query has to be adjusted (via a switch) based on the params. The query returns a complete set of ID's, sorted the desired way, then a range of the ID's are grabbed and used to fetch all the data needed to display a "page" of records for a grid.

Thanks, Jack

BTW, here's the modified query I tried with your suggestion (in case maybe I missed something):

exec sp_executesql N'
SELECT DISTINCT 
[i].[MailboxItemID] AS [MailboxItemID]
 ,[Proposal].[Description] AS [ProposalName]
 ,map2.DateCreated
FROM 
[MailboxItem] [i]
INNER JOIN [MailboxItemMailboxItemStatusMap] 
 ON [i].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
INNER JOIN [ProposalMailboxItemMap] 
 ON  [i].[MailboxItemID]=[ProposalMailboxItemMap].[MailboxItemID]
INNER JOIN [Proposal] 
 ON [Proposal].[ProposalID]=[ProposalMailboxItemMap].[ProposalID]
LEFT JOIN [MailboxItemMailboxItemStatusMap] as map2
 ON map2.[MailboxItemID] = [i].[MailboxItemID]
WHERE 
( 
 map2.mailboxItemStatusID in (6,7)
 And [i].[MailboxID] = @MailboxID1 
 And [i].[CommitDate] IS NOT NULL 
 And [i].[IsDeleted] = @IsDeleted2 
 And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN ( @MailboxItemStatusID3, @MailboxItemStatusID4 ) 
 And NOT [i].[MailboxItemID] IN 
 (
  SELECT [MailboxItemMailboxItemStatusMap].[MailboxItemID] AS [MailboxItemID] 
  FROM 
  ( 
   [MailboxItem] 
   INNER JOIN [MailboxItemMailboxItemStatusMap] 
    ON [MailboxItem].[MailboxItemID]=[MailboxItemMailboxItemStatusMap].[MailboxItemID]
  ) 
  WHERE ( [MailboxItem].[MailboxID] = @MailboxID5 
  And [MailboxItemMailboxItemStatusMap].[MailboxItemStatusID] IN (@MailboxItemStatusID6)
 )
)
) 
ORDER BY [Proposal].[Description] 
ASC',N'@MailboxID1 int,@IsDeleted2 bit,@MailboxItemStatusID3 smallint,@MailboxItemStatusID4 smallint,@MailboxID5 int,@MailboxItemStatusID6 
smallint',@MailboxID1=112,@IsDeleted2=0,@MailboxItemStatusID3=2,@MailboxItemStatusID4=5, @MailboxID5=112,@MailboxItemStatusID6=10

I tried that, too. It does not work, because of the way the same table is joined to more than once.

Do you mean you have tried it directly againest the database or implemented in LLBLGen code?

Notice that MailboxItemMailboxItemStatusMap gets joined to twice there. Also notice that in the WHERE clause there are various clauses that also filter on MailboxItemMailboxItemStatusMap. Basically the table has to be filtered on to get the outermost recordset (the list of distinct ID's that I need), but also has to return the DateCreated from MailboxItemMailboxItemStatusMap via a different filter on that table.

I see no problem in joining into the same table more than one, with the help of aliases, we can select which joined instance to filter upon.

I tried it directly against the db as shown in my last message. It returned only records where the map2 table had a DateCreated. IE, I got:

MailboxItemID  ProposalName                                DateCreated
2206                Reno Tahoe Blues Fest                  2006-03-08 02:33:48.330
2237                Starlight Starbright Texas Hold Em    2006-12-05 01:00:32.417
1464                Telluride Jazz Celebration                  2006-03-22 00:08:34.437

but what I want to get is:

MailboxItemID ProposalName                                             DateCreated
2206               Reno Tahoe Blues Fest                                  2006-03-08 02:33:48.330
1564               Roderick Green                                             NULL
1220               Saratoga Drama Group Annual Fundraiser    NULL
2237              Starlight Starbright Texas Hold Em                 2006-12-05 01:00:32.417
1181              State High School Athletic Championships   NULL
1464              Telluride Jazz Celebration                              2006-03-22 00:08:34.437

IE, if there's no match on the map2 part of the query, I need a NULL in there. I'm thinking that what's preventing that is the other parts of the WHERE clause that are also querying MailboxItemMailboxItemStatusMap, but am not sure. Since I have two other ways that work, but apparently can't be done via LLBL, I'm looking for help figuring out a 3rd way that will work. Thanks for your help so far.

Jack

Your subquery approach (the second query) is doable with a ScalarQueryExpression in v2. It's a bit sad you use v1.x because in v1.x you don't have the scalar query expression.

I find the queries a bit hard to follow. So I'd like you to explain the table structure, relations and which fields you want to obtain from them.

IMHO, scalar subqueries in selectlists are rewritable as joins, however it sometimes is very tricky. So as you NEED a rewrite of the query to make it work with llblgen code v1.x, as you can't use a scalar subquery in the select list, it's essential to get the right info and I miss that as I find it hard to reverse engineer relational models from complex queries.

Thanks for looking at this more. We are bound to 1.x for this project right now, but thanks for pointing out that there's a way to do it in 2.x. Here are the relevant parts of the tables in the datamodel. All ID's are either int or smallint datatype:

MailboxItem
- MailboxItemID (pk)
- CommitDate (datetime)
- IsDeleted (bit)

MailboxItemStatus
- MailboxItemStatusID (pk)

MailboxItemMailboxItemStatusMap
- MailboxItemID (fk to MailboxItem table)
- MailboxItemStatusID (fk to MailboxItemStatus table)
- DateCreated (datetime)

(MailboxItemID + MailboxItemStatusID are a compound PK)

Proposal
- ProposalID (pk)
- Description (nvarchar)

ProposalMailboxItemMap
- ProposalID (fk to Proposal)
- MailboxItemID (fk to MailboxItem)

(ProposalID and MailboxIteMID are a compound PK)

Aside from needing a resultset (of unique MailboxItemID's) sorted by MailboxItemMailboxItemStatusMap.DateCreated where MailboxItemMailboxItemStatusMap.MailboxItemStatusID in (6,7), the outermost part of the query also has to do filtering on a different subset of MailboxItemStatusID's from the same table (MailboxItemMailboxItemStatusMap).

Hope this helps clarify! It seemed to me like doing a LEFT JOIN like was suggested above would work, because it would treat the 2nd instance of the MailboxItemMailboxItemStatusMap table like it was a different table than the 1st instance of it, but there seems to be a conflict somewhere that results in a recordset where I only get records that match across the MailboxItem and MailboxItemMailboxItemStatusMap tables, rather than all the correct items from MailboxItem and either the DateCreated or NULL from the MailboxItemMailboxItemStatusMap table where MailboxStatusID in (6,7) would result in either a DateCreated or a NULL.

Thanks, Jack

I just did some more messing around with this and found a way that will work, but am not sure how to do it in LLBL 1.x. Instead of this:

LEFT JOIN [MailboxItemMailboxItemStatusMap] as map2
 ON map2.[MailboxItemID] = [i].[MailboxItemID]
WHERE
....
 AND map2.mailboxItemStatusID in (6,7))

This works:

LEFT JOIN [MailboxItemMailboxItemStatusMap] as map2
 ON (map2.[MailboxItemID] = [i].[MailboxItemID] AND map2.mailboxItemStatusID in (6,7))
WHERE .....

basically, I moved the IN clause into the LEFT JOIN itself (and out of the WHERE clause). This gave me a correct resultset. Can this be done in LLBL 1.x?

• Jack

I figured out a way to do what I want. After I discovered that I could do something like this using a CustomFilter on a Relation:

LEFT JOIN [MailboxItemMailboxItemStatusMap] as map2
ON (map2.[MailboxItemID] = [i].[MailboxItemID] AND map2.mailboxItemStatusID in (6,7))

I combined that with using aliases like this (notice the use of "map2"):

IPredicateExpression declineDateFilter = new PredicateExpression();
declineDateFilter.Add( PredicateFactory.CompareRange( MailboxItemMailboxItemStatusMapFieldIndex.MailboxItemStatusID, "map2", new short[]{6,7} ) );
filter.Relations.Add( MailboxItemEntity.Relations.MailboxItemMailboxItemStatusMapEntityUsingMailboxItemID, "map2", JoinHint.Left ).CustomFilter = declineDateFilter;

ResultsetFields fields = new ResultsetFields(2); 

fields.DefineField( MailboxItemFieldIndex.MailboxItemID, 0, "MailboxItemID");
fields.DefineField( MailboxItemMailboxItemStatusMapFieldIndex.DateCreated, 1, "DeclineDate", "map2");

sorter = new SortExpression( SortClauseFactory.Create( MailboxItemMailboxItemStatusMapFieldIndex.DateCreated, (sortOrder == SortOrder.Ascending) ? SortOperator.Ascending : SortOperator.Descending, "map2" ) );

The "map2" alias had to be set in 3 places so that the data from the correct instance of MailboxItemMailboxItemStatusMap was used:

1. in the code that creates the LEFT JOIN
2. in the ResultsetFields for the DateCreated col

3. in the sorter

4. Jack