In my projects form I pull contact history out and display it as the user scrolls down the list. When there is a contact the selection clause is _fkContacts = :ContactID. When there is no contact the clause is _fkCompanies = :CompanyID.

The only way I can think of is to unprepare the query, replace the selection string and off we go again. This is a bit lumpy where the changeover occurs so I'm looking for a better solution (I've thought of having two queries and flipping the datasource for the display grid) but I'm sure someone out there must be clever enough to think of something - he says hopefully

Roy Lambert

SELECT h.HistoryField, c.ContactField, m.CompanyField
FROM HistoryTable h
LEFT JOIN ContactTable c ON h._fkContacts = c.ContactID
LEFT JOIN CompanyTable m ON m._fkCompanies = c.CompanyID

--
David Cornelius
Cornelius Concepts

On 7/13/2010 6:11 AM, Roy Lambert wrote:
> In my projects form I pull contact history out and display it as the user scrolls down the list. When there is a contact the selection clause is _fkContacts = :ContactID. When there is no contact the clause is _fkCompanies = :CompanyID.
>
> The only way I can think of is to unprepare the query, replace the selection string and off we go again. This is a bit lumpy where the changeover occurs so I'm looking for a better solution (I've thought of having two queries and flipping the datasource for the display grid) but I'm sure someone out there must be clever enough to think of something - he says hopefully
>
> Roy Lambert

Of course, this was assuming the ID fields are in the "history" table.
If not, you might need to use a UNION statement instead.

However, after saying all this, I'm sure you had already thought of this
approach, so I'm probably not helping too much.  Is there more
information you could share about the structure of the tables?

--
David Cornelius
Cornelius Concepts

On 7/13/2010 7:29 AM, David Cornelius wrote:
> SELECT h.HistoryField, c.ContactField, m.CompanyField
> FROM HistoryTable h
> LEFT JOIN ContactTable c ON h._fkContacts = c.ContactID
> LEFT JOIN CompanyTable m ON m._fkCompanies = c.CompanyID
>

Roy,

<< In my projects form I pull contact history out and display it as the user
scrolls down the list. When there is a contact the selection clause is
_fkContacts = :ContactID. When there is no contact the clause is
_fkCompanies = :CompanyID. >>

This will completely confuse the optimizer and make the query un-optimized,
but:

SELECT * FROM History
WHERE IF(:ContactID IS NULL, _fkCompanies = :CompanyID, _fkContacts =
:ContactID)

--
Tim Young
Elevate Software
www.elevatesoft.com

David


Good try but no kwepie doll

Roy Lambert

David


>However, after saying all this, I'm sure you had already thought of this
>approach, so I'm probably not helping too much. Is there more
>information you could share about the structure of the tables?

Think of two tables - calls to be made (Calls) and email, letters & notes inc conversations (ELN). The relevant portion of each is:

Calls:
_CallID:integer
_fkContacts:integer
_fkCompanies:integer

ELN
_ID
_fkContacts:integer
_fkCompanies:integer

_fkCompanies = foreign key to companies
_fkContacts = foreign key to contacts

The way it works is that the user works through the calls to be made - downwards, upwards, randomly etc. Part of the information displayed is any previous contact (in the sense of ELN not people) history. If there is a contact linked to the call (not _Calls_fkContacts.IsNull) only the contacts ELN is displayed, but this may include history from previous companies, if there is no contact linked then all history for the company is displayed regardless of contact, but only for that company.

Hence the WHERE clause can flip between _fkContacts = :_fkContacts and _fkCompanies = :_fkCompanies with the parameter being derived from Calls.

Hope that explains it.

Roy Lambert

Tim


>SELECT * FROM History
>WHERE IF(:ContactID IS NULL, _fkCompanies = :CompanyID, _fkContacts =
>:ContactID)

Neater than my idea

WHERE
(:ContactsWanted AND _fkContacts = :_fkContact) OR (:CompanyWanted AND _fkCompanies = :_fkCompanies)

but on the basis of speed I'll give both a miss. Guess I'll have to live with the lumpiness

Roy Lambert

This makes a lot of sense now.  Tim had a nifty idea (I didn't know you
could have IFs in a WHERE clause), but I think your original idea of
flipping between two prepared queries will probably be fastest.

--
David Cornelius
Cornelius Concepts

On 7/13/2010 11:42 PM, Roy Lambert wrote:
> David
>
>
>> However, after saying all this, I'm sure you had already thought of this
>> approach, so I'm probably not helping too much. Is there more
>> information you could share about the structure of the tables?
>
> Think of two tables - calls to be made (Calls) and email, letters&  notes inc conversations (ELN). The relevant portion of each is:
>
> Calls:
> _CallID:integer
> _fkContacts:integer
> _fkCompanies:integer
>
> ELN
> _ID
> _fkContacts:integer
> _fkCompanies:integer
>
> _fkCompanies = foreign key to companies
> _fkContacts = foreign key to contacts
>
> The way it works is that the user works through the calls to be made - downwards, upwards, randomly etc. Part of the information displayed is any previous contact (in the sense of ELN not people) history. If there is a contact linked to the call (not _Calls_fkContacts.IsNull) only the contacts ELN is displayed, but this may include history from previous companies, if there is no contact linked then all history for the company is displayed regardless of contact, but only for that company.
>
> Hence the WHERE clause can flip between _fkContacts = :_fkContacts and _fkCompanies = :_fkCompanies with the parameter being derived from Calls.
>
> Hope that explains it.
>
> Roy Lambert

Roy,

<< Neater than my idea >>

Not really - your idea can be optimized, while mine can't.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Not really - your idea can be optimized, while mine can't.

I recall that the last time I had a query where I did this sort of thing it defaulted to a row scan because of the True/False parameter. Has this changed?

Roy Lambert