Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SQL question
Message
 
 
To
06/08/2009 11:00:34
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Environment versions
Visual FoxPro:
VFP 9 SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01416401
Message ID:
01416502
Views:
61
>>>see message#1416401 for a simple way in vfp
>>
>>This message points to the original question - which message did you mean?
>message#1416405

As I said, it doesn't solve the problem we want to solve - we only want to return non-matching values. BTW, my solution was identical to yours (I used inner join), but it doesn't achieve our goal.
	create cursor Schnaps ;
	(	c1	c(1), ;
		c2	c(1),; 
		c3	c(1) ;
	)
	
	insert into Schnaps values ('A', 'a', '1')
	insert into Schnaps values ('A', 'b', '2')
	insert into Schnaps values ('A', 'b', '3')
	insert into Schnaps values ('B', 'c', '4')
	insert into Schnaps values ('B', 'c', '4')
	insert into Schnaps values ('B', 'd', '4') && Need to return this - no match
	insert into Schnaps values ('B', 'e', '4') && added
	insert into Schnaps values ('B', 'e', '4') && added
	insert into Schnaps values ('B', 'f', '4') && added  && Need to return this - no match
	insert into Schnaps values ('A', 'b', '3')
	
*!*		select T.* from Schnaps T INNER JOIN ;
*!*		(SELECT c3, MIN(c1) as c1_min,MAX(c1) as c1_max, ;
*!*		MIN(c2) as c2_min, MAX(c2) as c2_max FROM Schnaps X group by 1 ;
*!*		HAVING COUNT(distinct(c1+c2)) > 1) X ON T.c3= X.c3 
	
	select S.* ;
		from Schnaps S, ;
			( ;
				select c3 ;
					from Schnaps ;
					group by  1 ;
					having ( count( distinct c1 + c2 ) > 1) ;
			) AA ;
			where	( S.c3 == AA.c3 )
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform