All,

I am wondering if the following is possible while doing an EntityCollection fetch.

This query essentially fetches all rows that have duplicates based on two non-PK fields--batch_id and Field1.

SELECT * FROM staging_location sl
WHERE location_identity IN
(
SELECT
    location_identity
FROM
    staging_location s
    INNER JOIN (
                SELECT
                    field1,
                    batch_id                    
                FROM
                    staging_location
                GROUP BY
                    field1,
                    batch_id
                HAVING
                    COUNT(*) > 1
               ) sub ON sub.field1 = s.field1
                        and sub.batch_id = s.batch_id
)

It would also work by removing the outermost query:

SELECT
    *
FROM
    staging_location s
    INNER JOIN (
                SELECT
                    field1,
                    batch_id                    
                FROM
                    staging_location
                GROUP BY
                    field1,
                    batch_id
                HAVING
                    COUNT(*) > 1
               ) sub ON sub.field1 = s.field1
                        and sub.batch_id = s.batch_id

I tried to mimic Frans's post in this thread, which is why I included the IN and identity column in the first query. I'm not sure which way is easier to translate to LLBL:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5063&HighLight=1

I'm pretty sure I could handle this with a dynamic list, but I'm not sure how to do this in an entity collection fetch.

Any help would be much appreciated.

Thanks,

Phil

Can the query be simplified to the following form?

SELECT
    s1.field1, s1.field2,....
FROM
    staging_location s1
    INNER JOIN 
    staging_location s2
    ON s1.field1 = s2.field1
    AND s1.batch_id = s2.batch_id
    AND s1.PK <> s2.PK

Walaa wrote:

Can the query be simplified to the following form?

SELECT
    s1.field1, s1.field2,....
FROM
    staging_location s1
    INNER JOIN 
    staging_location s2
    ON s1.field1 = s2.field1
    AND s1.batch_id = s2.batch_id
    AND s1.PK <> s2.PK

It sure can, and as and added bonus I actually know how to convert this to a collection fetch.

Thanks Walaa!