BorlandTalk.com

Bill C · Guest

G’day all
D7 Pro, MS Server 7, ADODatasets, new to SQL

My app has a master and 3 detail ADODatasets and the user is
allowed to ‘construct’ an SQL query by clicking on field names,
the operators (and, or, etc) and typing what they want to search
for. The actual SQL statement is constructed and validated
underneath all this as the user clicks the selections.

The SQL statement finishes up as…

SELECT * from Patients P
Left outer JOIN samples S on P.Link = S.LinkSam
Left Outer JOIN tests T on P.Link = T.LinkTests
Left outer JOIN prenatal R on P.Link = PNDLink
WHERE (upper(diagnosis) = ‘DISORDER’ )

This SQL actually looks just in the Master table as the user
doesn’t want to find anything else in the Tests, Samples or
Prenatal tables. There is no way I can predict whether the user
is going to want data from ALL details, just one or even just
the Master. Also, the details may or may not have data at all.

When the above SQL is executed via CommandText, the ADODataset
has multiple occurrences of the same patient record, I think
where there are multiple entries in the other detail tables.
Interestingly, this SQL seemed to work OK when I was using it on
Ttables.

Is there a way to stop this? Any other suggestions gratefully
received.
Thanks for your help
Cheers
Bill

Bill Todd (TeamB) · Guest

Just change the query to:

SELECT * from Patients P
WHERE (upper(diagnosis) = ‘DISORDER’ )

Since you are not selecting any columns from the detail tables there
is no reason to include the left outer joins.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Bill C · Guest

That's OK if the user chooses to only want the patients with
the DISORDER... but what if they want the Sample to be
= 'BLOOD' as well? So the SQL then becomes

SELECT * from Patients P
Left outer JOIN samples S on P.Link = S.LinkSam
Left Outer JOIN tests T on P.Link = T.LinkTests
Left outer JOIN prenatal R on P.Link = PNDLink
WHERE (upper(diagnosis) = ‘DISORDER’ ) and (upper(type) = 'BLOOD')

What I am trying to have is a generic SQL statement that covers
all options that the user MAY choose to search for from the
master and details. The SQL 'grows' as the user selects
different items from the detail tables
Sorry if I'm not clear.
Cheers
Bill

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote:

Bill Todd (TeamB) · Guest

Sorry. I misread the original SQL statement. The only way to cover
every option in a single query is to link in all of the fields from
all of the tables as you are doing. The only other alternative is to
dynamically build the SQL statement based on which tables and fields
the user has selected.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Bill C · Guest

Thanks Bill
I had come to the same conclusion and will dynamically build the SQL statement with more control ... I guess there are some things that just HAVE to be re-done from scratch when changing from a desktop app to Server7
Cheers
Bill

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote: