January 23, 2006 at 5:20 am
Hi
Tbl_A
ColA
Tbl_B
ColB
Tbl_C
ColA
ColB
Tables don’t have any relation.
In above table for my circumstances Tbl_C must have all combinations of Tbl_A.COlA & Tbl_B.Col.B (i.e. Tbl_C = Cartesian Product of Tbl_A & Tbl_B)
But I got some rows missing.
Can some one advise a query through which I can find combination of (Tbl_A.ColA & Tbl2_ColB) missing in Tbl_C
Wishes
Jawad
January 23, 2006 at 6:05 am
I have only a few minutes so I can't test it, but something along these lines should work. You create the "full" rowset (without missing records) using cross join, use it as a derived table, and then the LEFT JOIN should display rows that are missing in your Tbl_C. If that does not work, let us know - I hope I'll have more time later to look at it.
SELECT * FROM
(SELECT * FROM Tbl_A
CROSS JOIN Tbl_B) as X
LEFT JOIN Tbl_C C ON X.ColA = C.ColA AND X.ColB = C.ColB
WHERE C.ColA IS NULL OR C.ColB IS NULL
HTH, Vladan
/edit: Did some tests now and it works for me. I suppose that you don't need to check for 'partial' rows in Tbl_C - I mean rows that have e.g. ColA=1 and ColB NULL - and you simply want to see which rows are missing. If yes, this should return what you need./
January 23, 2006 at 6:50 am
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply