Self join query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to keep this query as 2 self joins; the Where statement is longer than
shown and recursively queries itself to enusre that a store has not already been used. Have just realized that line 2 of the where statement is not filtering properly,
and is allowing 2 stores to be equal.

Am I missing something ?

Thanks,
Mike

PARAMETERS [City] Text ( 255 );
INSERT INTO TEST3 ( COUNTY, STORE, ACV1, CITY1, STORE2, ACV2, CITY2, STORE3, ACV3, CITY3 )
SELECT TOP 1 E1.COUNTY, E1.STR AS STORE, E1.ACV, E1.CITY AS CITY1, E2.STR AS STORE2, E2.ACV AS ACV2, E2.CITY AS CITY2, E3.STR AS STORE3, E3.ACV AS ACV3, E3.CITY AS CITY3
FROM DATA1 AS E1, DATA1 AS E2, DATA1 AS E3
WHERE ( E1.[CITY]=[City] AND E2.[CITY]=[City] AND E3.[CITY]=[City] ) AND
( E1.[STR]<> E2.[STR] And E1.[STR]<> E3.[STR] And E2.[STR]<> E3.[STR] ) ;
 
MK said:
I need to keep this query as 2 self joins; the Where statement is longer than
shown and recursively queries itself to enusre that a store has not already been
Click to expand...
used. Have just realized that line 2 of the where statement is not filtering
properly,
and is allowing 2 stores to be equal.

Am I missing something ?

Thanks,
Mike

PARAMETERS [City] Text ( 255 );
INSERT INTO TEST3 ( COUNTY, STORE, ACV1, CITY1, STORE2, ACV2, CITY2, STORE3, ACV3, CITY3 )
SELECT TOP 1 E1.COUNTY, E1.STR AS STORE, E1.ACV, E1.CITY AS CITY1, E2.STR AS
Click to expand...
STORE2, E2.ACV AS ACV2, E2.CITY AS CITY2, E3.STR AS STORE3, E3.ACV AS ACV3, E3.CITY
AS CITY3
FROM DATA1 AS E1, DATA1 AS E2, DATA1 AS E3
WHERE ( E1.[CITY]=[City] AND E2.[CITY]=[City] AND E3.[CITY]=[City] ) AND
( E1.[STR]<> E2.[STR] And E1.[STR]<> E3.[STR] And E2.[STR]<>
Click to expand...
E3.[STR] ) ;

Hi MK,

Are you not showing an "OR" clause?

If any STR was Null, what you have shown
would evaluate to Null. If that Null result
were OR'd with some other condition,

Null OR True = True <--return record even though 2 other STR's might be equal
Null OR False = Null <--do not return record

You don't say if you use only the WHERE clause
you have shown, whether you still can get 2 equal stores.

Sorry...only thing I could think of...

Good luck,

Gary Walter
 
Back
Top