Thread: Comparing Subsets III

As large as it may appear, that really is only a Select statement. However, I guess it's possible that your query tool could muck about with it somehow and not make it work. In that case, you could replace the CTE's in my query with Views that you store on the Server. Then, with your query tool, just do that last SELECT..UNION statement.

1. No, I made all the numbers up for example -- I like to use different ranges for different things so it make following (for me at least) easier.

2. Indeed, the PK on the M table has nothing to do with the PK on either V or N. In my example the M table has a PK called MExID which can be an AutoIncrement field. ExcID1 and ExcID2 then are FK to the V or N table as necessary.

Thus to JOIN to the M table the ON clause could/would be

Code:

 
   On V.ID = M.ExcID1 or V.ID = M.ExcID2

Left Right or Otherwise.

Not quite. I screwed up the ON clause in #23. Going back to your query in #22, I think the query you want is:

Code:

;WITH

V (ID, EXCID)
AS (SELECT * FROM (SELECT 10 AS ID, 1 AS EXCID
UNION ALL
SELECT 10 AS ID, 2 AS EXCID) V)
,
N (ID, EXCID)
AS (SELECT * FROM (SELECT 13 AS ID, 3 AS EXCID
UNION ALL
SELECT 13 AS ID, 3 AS EXCID
UNION ALL
SELECT 13 AS ID, 4 AS EXCID
UNION ALL
SELECT 14 AS ID, 2 AS EXCID
UNION ALL
SELECT 14 AS ID, 3 AS EXCID
UNION ALL
SELECT 15 AS ID, 3 AS EXCID
UNION ALL
SELECT 15 AS ID, 6 AS EXCID
UNION ALL
SELECT 17 AS ID, 6 AS EXCID) N),
M (ID, EXCID1, EXCID2)
AS (SELECT * FROM (SELECT 21 AS ID, 2 AS EXCID1, 4 AS EXCID2
UNION ALL
SELECT 22 AS ID, 1 AS EXCID1, 5 AS EXCID2 ) M)
-- All Above Same
Select V.*, M.ID, 1 as Visible
 From V
 Left Join M
   on (V.ExcID = M.Excid1 or V.ExcID = M.Excid2)
Union
Select N.*, M.ID, 0 --as NotVisible
  From N
  Left Join M
   on (N.ExcID = M.Excid1 or N.ExcID = M.ExcId2)

Sort of, at this point we have combined the V and N tables into 1 table rather than having them separate through the query. After all the previous fun, you should be at the point in my post #17 Comparing Subsets III where you see 'Now the fun begins'

However, that query is still using the V and N tables with the additional column (MExID I believe). So, to make the remaining query work, substitute the following for V and N (in the query from #26 above that is)

Code:

-- V:
   , NEW_V AS (
    Select from <resultsOfPreviousQuery> Where Visible = 1)
-- N:
  , NEW_N AS (
    Select from <resultsOfPreviousQuery> Where Visible = 0)

That should make it work.

Ok, I got it! (I think )

I reworked it a bit to put my data in so I can use it. And I switched the "not in" clauses around in the Safe_n and Unsafe N because this way made more sense to me. I also populated a MEX id for everything in N and V. Could you take a look and let me know if you think it's correct? I needed to do a cross join between V and Safe_N because I wanted to see all combinations of Safe_N that can coexist with V. Any suggestions or notes for possible pitfalls will be greatly appreciated...again, thanks for sticking thru this with me...in the end this is going to save a ton of time!

Code:

;WITH

V (ID, EXCID, MEX)
AS (SELECT * FROM (

SELECT 3 AS ID, 3 AS EXCID, 100 AS MEX
UNION ALL
SELECT 3 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 7 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 7 AS ID, 3 AS EXCID, 100 AS MEX
UNION ALL
SELECT 7 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 8 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 8 AS ID, 5 AS EXCID, 100 AS MEX
UNION ALL
SELECT 8 AS ID, 7 AS EXCID, 200 AS MEX
UNION ALL
SELECT 18 AS ID, 2 AS EXCID, NULL AS MEX
UNION ALL
SELECT 18 AS ID, 3 AS EXCID, 100 AS MEX
UNION ALL
SELECT 18 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 26 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 26 AS ID, 7 AS EXCID, 100 AS MEX
UNION ALL
SELECT 26 AS ID, 8 AS EXCID, NULL AS MEX) V)
,

N (ID, EXCID, MEX)
AS (SELECT * FROM (
SELECT 6 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 9 AS ID, 3 AS EXCID, 100 AS MEX
UNION ALL
SELECT 9 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 11 AS ID, 2 AS EXCID, NULL AS MEX
UNION ALL
SELECT 13 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 16 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 19 AS ID, 2 AS EXCID, NULL AS MEX
UNION ALL
SELECT 19 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 20 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 20 AS ID, 7 AS EXCID, 100 AS MEX
UNION ALL
SELECT 21 AS ID, 3 AS EXCID, 100 AS MEX
UNION ALL
SELECT 22 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 22 AS ID, 7 AS EXCID, 100 AS MEX
UNION ALL
SELECT 23 AS ID, 7 AS EXCID, 100 AS MEX
UNION ALL
SELECT 24 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 25 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 25 AS ID, 5 AS EXCID, 200 AS MEX
UNION ALL
SELECT 27 AS ID, 2 AS EXCID, NULL AS MEX
UNION ALL
SELECT 27 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 29 AS ID, 1 AS EXCID, NULL AS MEX
UNION ALL
SELECT 29 AS ID, 6 AS EXCID, 200 AS MEX
UNION ALL
SELECT 29 AS ID, 7 AS EXCID, 100 AS MEX) N)
,
M (ID, EXCID1, EXCID2)
AS (SELECT * FROM (
SELECT 100 AS ID, 3 AS EXCID1, 7 AS EXCID2
UNION ALL
SELECT 200 AS ID, 5 AS EXCID1, 6 AS EXCID2 ) M)
-- All Above Same
,
SAFE_N (ID, EXCID, MEX)
AS (SELECT ID, EXCID, MEX FROM N WHERE ID IN (SELECT DISTINCT ID FROM N WHERE MEX IS NULL))
,
UNSAFE_N (ID, EXCID, MEX)
AS (SELECT ID, EXCID, MEX FROM N WHERE ID NOT IN (SELECT DISTINCT ID FROM SAFE_N))
,
OK_N_ID (ID, EXCID, MEX)
AS (SELECT DISTINCT UNSAFE_N.ID, UNSAFE_N.EXCID, UNSAFE_N.MEX FROM V JOIN UNSAFE_N ON (UNSAFE_N.MEX=V.MEX AND V.EXCID=UNSAFE_N.EXCID))
,
OK_N (ID, EXCID, MEX)
AS (SELECT * FROM N WHERE ID NOT IN (SELECT ID FROM OK_N_ID))

SELECT V.ID, SAFE_N.ID  FROM V CROSS JOIN SAFE_N

GROUP BY V.ID, SAFE_N.ID

If I get a chance today, I'll take a look through it -- but if its working for you, great. I'm literally writing 3 different applications in 3 different languages right now, I think my brain is going to explode.