CRecordset join query issues 
Author Message
 CRecordset join query issues

Hi all.

I am fairly new to the MFC's, so any comments about this would be
helpful.

I am using an ODBC connection to an Access file (.MDB).  I have my data
stored in several tables in a few cases, which means that when I perform
queries, I must sometimes span multiple tables.  

My solution was to create a CRecordset that joins all of the tables that
might be needed to search on a particular subject.  Then I generate my
own SQL Select statement from scratch.  The problem is that if I don't
specify conditions for every table included in the join, CRecordset
chokes.  

For example, I have 3 tables: applicant, phone, degrees.  An applicant
could potentially have a phone entry, but not a degree entry.  If I only
attempt to query the applicant and phone table, CRecordset dies. "Error
retrieving record.", specifically.

I am open to SQL workarounds.  Also, please tell me if I am way
off-base!  This seems too fundamental to database functionality to be
having this kind of problem....

Sorry for the long windedness.

Thanks,
Brantley
Venux Technology Group



Sat, 31 Aug 2002 03:00:00 GMT  
 CRecordset join query issues
First, as you know probably know, you want your Recordset set to always
contain the same columns.

So to do this with SQL, you probably want to use a LEFT JOIN.  A LEFT JOIN
will insure you get back AT LEAST the applicant info, even if there is
nothing appropriate in the phone and degree tables.  The columns for the
other tables will be NULL if not appropriate data is available.  For
example:

SELECT A.name, B.ext, C.school FROM applicant A LEFT JOIN phone B ON A.id =
B.id LEFT JOIN degrees C ON A.id = C.id WHERE A.id=3823984

So even if the applicant (and I'm assumeing the A.id=3823984 is a valid
applicant) is the only table with data in it, you are assured of getting
back A.name, where B.ext and C.school will be returned with values of NULL.


Quote:
> Hi all.

> I am fairly new to the MFC's, so any comments about this would be
> helpful.

> I am using an ODBC connection to an Access file (.MDB).  I have my data
> stored in several tables in a few cases, which means that when I perform
> queries, I must sometimes span multiple tables.

> My solution was to create a CRecordset that joins all of the tables that
> might be needed to search on a particular subject.  Then I generate my
> own SQL Select statement from scratch.  The problem is that if I don't
> specify conditions for every table included in the join, CRecordset
> chokes.

> For example, I have 3 tables: applicant, phone, degrees.  An applicant
> could potentially have a phone entry, but not a degree entry.  If I only
> attempt to query the applicant and phone table, CRecordset dies. "Error
> retrieving record.", specifically.

> I am open to SQL workarounds.  Also, please tell me if I am way
> off-base!  This seems too fundamental to database functionality to be
> having this kind of problem....

> Sorry for the long windedness.

> Thanks,
> Brantley
> Venux Technology Group



Sun, 01 Sep 2002 03:00:00 GMT  
 CRecordset join query issues
Thx for the suggestion.  This seems to be a workable solution, but I
can't for the life of me figure out how to perform a LEFT JOIN operation
on multiple tables.  Jet chokes when you have more than one  LEFT JOIN
in a single query.  I tried separating with commas and using AND.

I really appreciate the help, tho!

Thanks again,
Brantley



Sun, 01 Sep 2002 03:00:00 GMT  
 CRecordset join query issues
Jet definitely can handle multiple LEFT JOINs.  Your SQL statement probably
has a syntax error in it.  If you have Access, test it out by setting up a
query and pasting your code into the SQL Text.  You could even create your
SQL statement via Access.  Then just paste that into your code.

Otherwise, just follow this template:

SELECT ... FROM tableA LEFT JOIN tableB ON ...condition... LEFT JOIN tableC
ON ...condition... WHERE ...condition...

There are no commas, AND, or anything else.  You might need to put brackets
around your joins...

    FROM (tableA a LEFT JOIN tableB b ON a.Id = b.ID) LEFT JOIN tableC ON
a.ID = c.ID
    WHERE a.ID = 89385


Quote:
> Thx for the suggestion.  This seems to be a workable solution, but I
> can't for the life of me figure out how to perform a LEFT JOIN operation
> on multiple tables.  Jet chokes when you have more than one  LEFT JOIN
> in a single query.  I tried separating with commas and using AND.

> I really appreciate the help, tho!

> Thanks again,
> Brantley



Sun, 01 Sep 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. CRecordset Join issue

2. ADO AddNew() with INNER JOIN query

3. CRecordset - Join statments and dbSnapshots???

4. Using CRecordSet for ODBC joins

5. CRecordset (ODBC) + Join = ReadOnly?

6. CRecordset and joining tables

7. CRecordset updateable join with SQL Server 7.0

8. Join using CRecordset

9. Performing join with CRecordset

10. Performing join with CRecordset

11. CRecordset::GetFieldValue() for dataset generated by a join condition

12. Problem writing to two database tables that are joined ( CRecordSet read-only)

 

 
Powered by phpBB® Forum Software