"Positioned request cannot be performed because result set was generated by a join condition" ... He

When I open RecordSet with SQL "SELECT * FROM master_table,detail_table WHERE master_table.primary_key = detail_table.foreign_key" MFC return error
"Positioned request cannot be performed because result set was generated by a join condition".
What I must do ??? Help Me ([email protected])

What records do you actually want the recordset to contain after the query is performed?

It seems to me that you may need the SQL command JOIN somewhere in your SQL statement.

The description of a problem and path of solution.
--------------------------------------------------

I have two joined tables.
First table (master) contain information about persons.
CREATE TABLE person (
objId SERIAL NOT NULL ,
first_name CHAR(15) ,
second_name CHAR(15) ,
burnDate DATE ,
PRIMARY KEY (objId)
);

Second table (detail) contain Photo (column named 'shot') of the person.
CREATE TABLE Shots (
shotId SERIAL NOT NULL ,
objId INTEGER NOT NULL ,
Shot BYTE NOT NULL ,
PRIMARY KEY (shotId)
);

Tables joined by constraint.
ALTER TABLE Shots
ADD CONSTRAINT FOREIGN KEY (objId) REFERENCES person;

I have create two tables, because the person can have not a photo,
or have one photo, or have more than one photos.

For example, I want to find all photos of man whose name is JOHN,
but I precisely do not know a second name (rezult may be NOTFOUND).

The recordset opens by the operator:
CRecordset::Open(CRecordset::snapshot, NULL, CRecordset::readOnly);

The SQL-string is passed through the function GetDefaultSQL.

I have tried some ways, varying value of SQL-string:

1) The most simple path (excellently works on 4GL)

SELECT first_name, second_name, burndate, shot
FROM person,OUTER shots
WHERE person.objid = shots.objid
AND first_name = "JOHN"

When I open recordset MFC return error
"Positioned request cannot be performed because result set
was generated by a join condition"

2) "Positioned request" used for unique identification of
each record of selected tables (person and shots), therefore
I have altered SQL-string.

SELECT person.objid person_objid, // Primary key of table person
person.first_name first_name,
person.second_name second_name,
person.burnDate burndate,
shots.shotId shotid, // Primary key of table shots
shots.objId shots_objid, // Foreign key of table shots referencing to person
shots.shot shot
FROM person,OUTER shots
WHERE person.objid = shots.objid
AND person.first_name = "JOHN"

MFC return error again
"Positioned request cannot be performed because result set
was generated by a join condition".

3) I have tried to remove a BYTE field (named 'shot') from SQL-string

SELECT person.objid person_objid, // Primary key of table person
person.first_name first_name,
person.second_name second_name,
person.burnDate burndate,
shots.shotId shotid, // Primary key of table shots
shots.objId shots_objid // Foreign key of table shots referencing to person
FROM person,OUTER shots
WHERE person.objid = shots.objid
AND person.first_name = "JOHN"

That for a miracle! The errors are not present!
However desirable result does not achieve,
the photos are not obtained.

4) I have altered string again

SELECT shots.shotId shotid, // Primary key of table shots
shots.objId shots_objid // Foreign key of table shots referencing to person
FROM shots
WHERE EXISTS (SELECT *
FROM person
WHERE person.objid = shots.objid
AND person.first_name = "JOHN")

Works again! The errors are not present!
However desirable result does not achieve,
the second name is not obtained.

5) Then I have tried to divide SQL into 2 parts (two recordset variables)
First (master) recordset opens with SQL:
SELECT person.objid person_objid, // Primary key of table person
person.first_name first_name,
person.second_name second_name,
person.burnDate burndate
WHERE person.first_name = "JOHN"

When I move on selected records ( function CRecordset::Move() )
opens other (detail) recordset with SQL
SELECT shots.shotId shotid, // Primary key of table shots
shots.objId shots_objid // Foreign key of table shots referencing to person
FROM shots
WHERE shots.objid = <integer value> // Value obtained from master recordset

Works again! The errors are not present!
However ... In this case demands more resources on opening additional recordset.

6) Exists as well SQL-solution of this problem
At database engine create view:
CREATE VIEW shots_view AS
SELECT person.objid objid,
person.first_name first_name,
person.second_name second_name,
person.burnDate burndate,
shots.shotId shotid,
shots.shot shot
FROM person,OUTER shots
WHERE person.objid = shots.objid

Then in program open recordset with SQL
SELECT objid,
first_name,
second_name,
burndate,
shotid,
shot
FROM shots_view
WHERE shots_view.first_name = "JOHN"

It will work too.
---------------------------------------------------------
CONCLUSION:
After experiments I have understood:
1) MFC work with joined tables, containing no binary columns
2) MFC work with one table, containing binary columns
3) MFC NOT WORK with joined tables, containing binary columns

May be other opinions ???

Victor Vat.