Query tables for differences

[mralmackay]

Please can you help with an unmatched query I need to perform.

Not sure if it's possible to use unmatched query wizard as I'm actually
querying against 3 tables.

I have 3 tables with the following fields in each table:

Table1 Table2 Table3
Field1 UserID UserID UserRef
Field2 Profile UserRef Profile

I need to be able to query where there are items within table 1 that
don't appear in table3 (by using Table2 to link the UserID = UserRef).

Appreciate any help you can offer.

Thanks, Al ([email protected]).

 

[Adam Turner via AccessMonster.com]

Please can you help with an unmatched query I need to perform.

Not sure if it's possible to use unmatched query wizard as I'm actually
querying against 3 tables.

I have 3 tables with the following fields in each table:

Table1 Table2 Table3
Field1 UserID UserID UserRef
Field2 Profile UserRef Profile

I need to be able to query where there are items within table 1 that
don't appear in table3 (by using Table2 to link the UserID = UserRef).

Appreciate any help you can offer.

Thanks, Al ([email protected]).

Select *
From Table1
Where Table1.UserID NOT IN (Select * from Table3)
AND table1.UserID = table2.Ref

 

[Adam Turner via AccessMonster.com]

Please can you help with an unmatched query I need to perform.

Not sure if it's possible to use unmatched query wizard as I'm actually
querying against 3 tables.

I have 3 tables with the following fields in each table:

Table1 Table2 Table3
Field1 UserID UserID UserRef
Field2 Profile UserRef Profile

I need to be able to query where there are items within table 1 that
don't appear in table3 (by using Table2 to link the UserID = UserRef).

Appreciate any help you can offer.

Thanks, Al ([email protected]).

Sorry for that half baked query. But this should work

SELECT t1.UserID
FROMTable1 t1(NOLOCK)
JOIN Table2 t2(NOLOCK) ON t1.UserID = t2.UserRef
Where t2.UseRef NOT IN (Select t3.UserRef from Table3 t3(NOLOCK))

 

[Guest]

This might be more to your liking.
SELECT [Table-1].UserID, [Table-1].Profile
FROM ([Table-1] LEFT JOIN [Table-2] ON [Table-1].UserID = [Table-2].UserID)
LEFT JOIN [Table-3] ON [Table-2].UserRef = [Table-3].UserRef
WHERE ((([Table-3].UserRef) Is Null));

This checks if it is in the junction table at the same time.
SELECT [Table-1].UserID, [Table-1].Profile, IIf([Table-2].[UserID] Is Not
Null,"Yes","No") AS Junction
FROM ([Table-1] LEFT JOIN [Table-2] ON [Table-1].UserID = [Table-2].UserID)
LEFT JOIN [Table-3] ON [Table-2].UserRef = [Table-3].UserRef
WHERE ((([Table-3].UserRef) Is Null));