query

[lee]

orig dest facility id

a b 1211 x1
c b 2311 x2
d e 5432 x1
a1 b 4781 x3


id pc

x1 p
x2 c
x3 p


I have two tables T1 and T2 . These need to be joined and then only
those rows need to be selected where dest is same but orig is
different and also also facility is different. I need the following
output-- here- the first , second and the fourth rows are selected
since the destn is same which is b but the orig and the facility are
different in each case. Can you please give the join query with the
above condition. Thanks

output from the above two tables

orig dest facility pc
a b 1211 p
c b 2311 c
a1 b 4781 p

 

[John Spencer]

Perhaps the following will work for you.

SELECT Orig, Dest, Facility, PC
FROM T1 INNER JOIN T2
ON T1.ID =T2.ID
WHERE Dest IN (
SELECT Dest
FROM T1 INNER JOIN T2
GROUP BY Dest
HAVING Max(Orig) <> Min(Orig)
AND Max(Facility) <> Min(Facility))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County