master / detail select with union

[skydvrz]

I am trying to do a query that is a detail of a master query. The detail query is actually a union of two queries.

It looks something like this:

Code: Select all

SELECT tfl.teid,
       tfl.fid,
       wf.name,
       wf.filesize,
       wf.filedescription,
       wfmt.mimetype,
       wf.dlcount,
       wf.dllast,
       wf.adddate,
       wf.dropdead,
       wf.filekeywords,
       0 as tableloc
FROM trainingfilelookup tfl, webfiles wf,webfilemimetypes wfmt
where tfl.filetype=0 and tfl.fid=wf.id and wfmt.id=wf.mimetype

union

select teid,fid,
       wi.name,
       wi.filesize,
       "",
       wfmt.mimetype,
       wi.dlcount,
       wi.dllast,
       wi.added,
       null,
       null,
       1 as tableloc
from trainingfilelookup tfl, webimages wi, webfilemimetypes wfmt
where tfl.filetype=1 and tfl.fid=wi.id and wfmt.id=wi.mimetype

The problem is that when the master query changes, MyDAC is simply appending a "AND TEID=1" to the last where clause in the union. This lets the first SELECT in the union return all possible TEIDs - only the second SELECT is limited to the TEID I want.

How do I do a "live" master/detail query with a union and have the masterkey apply to all the selects in the union?

The master query is connected to one DB grid and the detail query is connected to another grid. When the master grid focused record changes, I need the detail grid to update.

Is there some way for the master query to autofill parameters in both SELECTs? Is there a better way to do this?

[AndreyZ]

Hello,

This is a difficult SQL statement and MyDAC cannot automatically determine all places where the WHERE clause should be. To solve the problem, you should not use the MasterFields and DetailFields properties (fill the MasterSource property only), and change your SQL code for the detail table to the following:

Code: Select all

SELECT tfl.teid,
       tfl.fid,
       wf.name,
       wf.filesize,
       wf.filedescription,
       wfmt.mimetype,
       wf.dlcount,
       wf.dllast,
       wf.adddate,
       wf.dropdead,
       wf.filekeywords,
       0 as tableloc
FROM trainingfilelookup tfl, webfiles wf,webfilemimetypes wfmt
WHERE tfl.filetype=0 and tfl.fid=wf.id and wfmt.id=wf.mimetype and teid=:teid

UNION

SELECT tfl.teid,
       tfl.fid,
       wi.name,
       wi.filesize,
       "",
       wfmt.mimetype,
       wi.dlcount,
       wi.dllast,
       wi.added,
       null,
       null,
       1 as tableloc
FROM trainingfilelookup tfl, webimages wi, webfilemimetypes wfmt 
WHERE tfl.filetype=1 and tfl.fid=wi.id and wfmt.id=wi.mimetype and teid=:teid

In this case the value for the teid parameter will be taken from the master table.