Joining a table to two different tables at the same time

rbuser

I am using D7, BDE and RB 7.04 end user designer.

I am assuming the answer is no... Is there a way to allow users to join a
table into a dataset against two different tables?

Something could probably be done using the SQL access functions/objects, but
I would like to do this via the Query Designer.

I've had to make custom "helper" views and add them to the data dictionary
to support this.

Any ideas?

nardmoseley

Using the DBDemos data included Delphi, can you provide an example SQL
statement that you would like to create?

rbuser

"Nard Moseley (Digital Metaphors)" wrote in

rbuser

Any luck on tracking this one down?

nardmoseley

Oops, I missed responding to this one by mistake.

- try moving that last condition to the Where clause. You can specify the
tablename.fieldname as the search condition. Here is the modified version...

select customer.Company, orders.OrderNo, orders.SaleDate, items.ItemNo,
Items.Qty
from customer
inner join orders on (orders.CustNo = customer.CustNo)
inner join items on (items.OrderNo = orders.OrderNo)
inner join parts on (parts.PartNo = items.PartNo)
inner join vendors on (vendors.VendorNo = parts.VendorNo)
Where (vendors.State = customer.State)
order by customer.company, orders.OrderNo


- using the same approach, your second example would be

select customer.Company, orders.OrderNo, orders.SaleDate
from customer
left outer join orders on (orders.CustNo = customer.CustNo)
where (orders.itemsTotal > 1000)
order by customer.company, orders.OrderNo

rbuser

"Nard Moseley (Digital Metaphors)" wrote in

nardmoseley

I would try creating a detail query linked to a master query. You can link
two dataviews by dragging the detail field to the master field. Then double
click on the visual link to access the Linking dialog and you can specify
that you want to include the master records that have no corresponding
detail. (You can also access the Link dialog by pressing the right mouse
button while positioned over the detail dataview and then selecting the Link
option from the context menu)

rbuser