VFP Filter on Rows Exact Match Only 2

[Asif Ali]

I want to filter exactly match rows from detail table. For Example in my filter cursor i have ('A101','B101') AssetCode
This is my detail Table

I am looking for MasterId of those AssetCode which are in my filter cursor.
Like MasterId 1 & 4 both having same AssetCode ('A101','B101') which is in my filter cursor.
My result should contain DetailID 1,2,9 and 11 with the same MasterID and AssetCode.
I don't want other Detail IDs because they are not fully matching with my Filter Cursor.
I have tried SubQuery and Joins but no use. Please help..

 

[Scott24x7]

Use the $ operator instead of = in your expression.

So instead of "A" = FieldName use "A" $ FieldName.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

First of all let me help you get that data out usable for those who'd like to answer:

Cd Getenv("TEMP")
Close Databases All
Erase tblDetails.*
Erase dbcDetails.*
Create Database dbcDetails
Create Table tblDetails (detailid Int, masterid Int, assetcode Char(4), Primary Key detailid Tag pid)
Index On masterid Tag xmstid
Index On assetcode Tag xasset

Insert Into tblDetails Values ( 1,1,'A101')
Insert Into tblDetails Values ( 2,1,'B101')
Insert Into tblDetails Values ( 3,1,'C101')
Insert Into tblDetails Values ( 4,1,'D101')
Insert Into tblDetails Values ( 5,2,'C101')
Insert Into tblDetails Values ( 6,2,'B101')
Insert Into tblDetails Values ( 7,3,'C101')
Insert Into tblDetails Values ( 8,3,'A101')
Insert Into tblDetails Values ( 9,4,'B101')
Insert Into tblDetails Values (10,4,'C101')
Insert Into tblDetails Values (11,4,'A101')

Create Cursor crsFilter (assetcode Char(4))
Insert Into crsFilter Values ('A101')
Insert Into crsFilter Values ('B101')

Then you can use this query thanks to VFP9s subquery capabilities:

Select tblDetails.* From tblDetails ;
   Inner Join crsFilter  On crsFilter.assetcode = tblDetails.assetcode ;
   Inner Join (Select masterid From tblDetails ;
   Inner Join crsFilter On crsFilter.assetcode = tblDetails.assetcode ;
   Group By masterid ;
   Having Count(*)= Reccount("crsFilter") ) tmpFilter2 ;
   On tmpFilter2.masterid = tblDetails.masterid ;
   Into Cursor crsResult

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

This does not add any functionality to Olaf's reply and does not improve on the results, it just avoids using VFP specific functions and aims at slightly broader SQL dialects compatibility.

SELECT dt.* FROM tblDetails dt ;
	INNER JOIN crsFilter ft ON ft.assetcode = dt.assetcode ;
	INNER JOIN (SELECT COUNT(*) AS cntFilter FROM crsFilter) ftc ON 1 = 1 ;
	INNER JOIN ;
		(SELECT mid.masterId, COUNT(*) AS cntMatch ;
			FROM tblDetails mid ;
				INNER JOIN crsFilter mft ON mft.assetcode = mid.assetcode ;
			GROUP BY mid.masterid) mtc ON mtc.masterId = dt.masterid AND mtc.cntMatch = ftc.cntFilter

 

[Olaf Doschke]

That's right, as often as I recommend keeping things compatible or transferrable, too.

But there's also a good reason to use specifics in any SQL dialect because excluding specifics you don't make use of strengths of the specific SQL dialect and you end up needing a higher level database because of that, self-fulfilling upgrade need. The VFP dependency just becomes very much too strong, if you call into user-defined functions. You can rely on things like SUBSTR() or LEFT()/RIGHT() existing in many databases, and more. You can also even reimplement functions as stored procedures in other databases, but surely VFP code does not easily transfer to the languages available for stored procedures in server databases.

In this case, I agree, as it's simple to avoid using RECCOUNT(), but I'd suggest using a variable instead, which you set in advance. Join ON 1=1 condition is neat, I also assume the VFP engine will not COUNT() as many times as there are records in the result or make use of the cached result of the first run, at least, but a variable would be much more straight forward. I decided for RECCOUNT("crsFilter") as it's also the cursor involved in joins and you can't forget to set this correctly, the only chance to fail is when the filter cursor contains deleted rows, which are included in reccount().

Variable syntax differs (with @ prefix in T-SQL and MySQL, for example), but it is easy to address and, last not least, when you change from inline VFP SQL to another database you need to transfer code with multiline continuations by semicolons to other RDBMS syntax anyway, even if you make use of ANSI compatible SQL.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Agreed, Olaf, there is much more to look after for when you must or want to elevate the compatibility levels of the SQL statements. The different roles of the semi-colon is a big problem that could be solved by TEXT .. ENDTEXT constructs with the appropriate PRETEXT value set, but the cases where even that could be blindly applied are far from unlimited.

 

[Asif Ali]

Thank you for helping me. But I am using VFP 7.0
I should mention it in my Question.

 

[Olaf Doschke]

Then you need to split this into two queries. (Alos rather using the approach of using a variable for the essential count to match):

LOCAL lnFiltercount
lnFiltercount = RecCount("crsFilter")

Select masterid From tblDetails ;
   Inner Join crsFilter On crsFilter.assetcode = tblDetails.assetcode ;
   Group By masterid ;
   Having Count(*) = m.lnFiltercount ;
   Into Cursor ; 
   crsFilter2

Select tblDetails.* From tblDetails  ;
   Inner Join crsFilter  On crsFilter.assetcode = tblDetails.assetcode ;
   Inner Join crsFilter2 On crsFilter2.masterid = tblDetails.masterid ;
   Into Cursor ; 
   crsResult

The only prererquisite to this is crsFilter has no deleted records, since it is a cursor under your control it should be created and filled with selections of a user only, so it won't have deleted rows reccount() also includes, as it only reads the record count reflecting the file lengtth in number of records including deleted ones for easy check of correct file length in table validation (which wouldn't be possible if the DBF file specifications had decided to decrese the count with deletions).

Just as sidenote: The split of the single query into two queries reveals you create a secondary filter cursor applied with INNER JOIN, too. This time it's about the masterids containing both wanted assets. You can also now query the full details of these masterids, not just the two assets, you may even just be satisfied with the condensed information these masterids have the two assets and take crsFilter2 as the end result already. Or, or, or...

Another solution would be first only inner joining crsFilter and then remove all the records where the masterid occurs not as often as necessary to have all assets in it, but that again would require what you do for crsFilter2. Done on a nonindexed result. It's better to apply two unindexed filter cursors on the indexed base tblDetails table, the only thing okay in doing a full table scan on a local cursor result is that it's in memory and local already. But that also means you then already loaded too much data via the bottleneck - the network. That also doesn't change when indexing a cursor. Which in itself is a full table scan already and just like the rushmore query optimizer engine does create temp indexes, only sometimes an index on a cursor is worth it to avoid multiple table scans.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Asif Ali]

Thank you Olaf.