mardi 15 août 2017

Unexpected result using CTE to perform a random join on two tables for all rows one-to-many

I am actually trying to figure out the best way to generate some random test data and in the process I have come across some strange behaviour with a CTE. Maybe someone can explain why it is happening; also, I would certainly appreciate any advice on what would be a better way to randomly join the rows of two tables (TableA and TableB) such that each row in TableA is joined to only one row in TableB and every row in TableB is joined to at least one row in TableA. For example, TableA has 5 distinct rows and TableB has 3 distinct rows. A random join should result in something like this:

TableA  TableB
1       3
2       1
3       1
4       2
5       1

It seems that it is necessary for some reason to use an interim table (@Q) to be able to ensure that a correct result is returned which has all rows from both TableA and TableB; just relying on the CTE results in not all the rows from TableB being included. I understand that sometimes no result is returned due to a failure of some kind in the cross apply which i have yet to identify and goes to the point that I am sure there is a better way to perform this operation. I hope that makes sense. Thanks in advance!

    declare @TableA table (
        ID int
        );
    declare @TableB table (
        ID int
        );
    declare @Q table (
        TrackRN int,
        TableAID int,
        TableBID int
        );

    with cte as (
        select
            1 as ID
        union all
        select
            ID + 1
        from cte
        where ID < 5
        )
    insert @TableA (ID)
    select ID from cte;

    with cte as (
        select
            1 as ID
        union all
        select
            ID + 1
        from cte
        where ID < 3
        )
    insert @TableB (ID)
    select ID from cte;

    select * from @TableA;
    select * from @TableB;

    with cte as (
        select
            row_number() over (partition by TableAID order by newid()) as TrackRN,
            TableAID,
            TableBID
        from (
            select
                a.ID as TableAID,
                b.ID as TableBID
            from @TableA as a
            cross apply @TableB as b
            ) as M
        )
    select --All rows from TableB not always included
        TableAID,
        TableBID
    from cte
    where TrackRN in (
        select
            top 1
                iCTE.TrackRN
        from cte as iCTE
        group by iCTE.TrackRN
        having count(distinct iCTE.TableBID) = (
            select count(1) from @TableB
            )
        )
    order by TableAID;

    with cte as (
        select
            row_number() over (partition by TableAID order by newid()) as TrackRN,
            TableAID,
            TableBID
        from (
            select
                a.ID as TableAID,
                b.ID as TableBID
            from @TableA as a
            cross apply @TableB as b
            ) as M
        )
    insert @Q
    select
        TrackRN,
        TableAID,
        TableBID
    from cte;

    select * from @Q;

    select --All rows from both TableA and TableB included
        TableAID,
        TableBID
    from @Q
    where TrackRN in (
        select
            top 1
                iQ.TrackRN
        from @Q as iQ
        group by iQ.TrackRN
        having count(distinct iQ.TableBID) = (
            select count(1) from @TableB
            )
        )
    order by TableAID;




Aucun commentaire:

Enregistrer un commentaire