> >It is a valid SQL statement, so it
> >should work, right?
>
> It's an ambiguous query, to begin with, but if you are not using Firebird
> then you will probably be allowed to submit it.

I am using Firebird, and it DOES allow me to submit it.

> This is the correct syntax:
>
> select * from products p
> left outer join vendorproducts v on
> p.prodbarcode=v.vpupc
> and p.prodsource=v.vpvendor
> where v.vpcost = 0
> or v.vpcost is null
> WHERE p.PRODBARCODE=? /* BIND_0 */

That last line was not part of my original query. IBO is generating that part
(for the master-detail maybe?). What is that ? /* BIND_0 */ about anyway?

> If your app expects single-row output then it's up to you as the programmer
> to parse the sql entered to ensure that it can't potentially return
> multiple rows.

I don't care about multiple rows. It's fine with me if it displays it multiple
times in my dataset. That's what the BDE did.

> >I do also have a "detail" TDBGrid, linking the above query to a
> >TIBOTable (vendorproducts) in a master/detail relationship
> >(MasterSource, etc.) on prodbarcode=vpupc. Could that be what's
> >complaining?
>
> Yes, that's what it's complaining about. If the master query returns two
> rows that have the same Keylinks then there will be one detail set with two
> potential master rows, violating the rules of a master-detail
> relationship. Master-detail is 1:Many and can't work with Many:Many.

Why not? It works fine with the BDE. When I run this query with the BDE, it
returns multiple rows in the master dataset, and they simply match to the exact
same rows in the detail dataset. That worked fine with me then. Why won't IBO
do the same?

> >TIBOTable doesn't have a JoinLinks property.
>
> TIBOTable cannot accommodate the output from a joined query, either. Are
> you saying that you are trying to use TIBOTable to do this?

No. I was saying the the DETAIL dataset is a TIBOTable. The MASTER dataset
(the output from the joined query) is a TIBOQuery.

> TIBOQuery (the only TDataset descendant that is valid for a query) has both
> Joinlinks and Keylinks and, furthermore, it cannot autodefine Keylinks for
> a joined set, even an unambiguous one.
>
> If you are using explicit join syntax (as in your example) then you don't
> use Joinlinks.
>
> >I tried
> >setting KeyLinks to VPVENDOR and VPVENDPARTNO (the key fields for that
> >table), but it didn't make a difference.
>
> The Keylinks are (need to be equivalent to) a primary key. With joined
> datasets (which you are presumably bringing back to a TIBOQuery somehow),
> you must define a Keylinks member for each column contributing to the
> unique key for the output set. As a minimum, this will be the primary keys
> of all tables but, in some cases you still won't get uniqueness without
> including more of the output columns.

Right. The query is outputting non-unique rows, so there's really no valid
Keylinks value that I could use that would make the dataset unique.

> As a **programmer** I would regard it as a challenge to set up something
> like this as a **persistent structure**, never mind attempt to build
> something like it based on a totally unknown master set constructed from
> user-defined, run-time building blocks. Good luck!

Ok. True. It would be near to impossible to make this master-detail
relationship work ALL THE TIME, when the user can enter any old ad hoc query
that they want. I accept that. My issue is that this used to work OK under the
BDE (or it made a reasonable interpretation, instead of throwing an exception).

The fact is that I wrote this query for a customer of mine to get a certain
result. I'm actually OK if I can find a different query that achieves the same
goal. Maybe a join isn't the best way to do it. Here's what I'm trying to do,
in plain english:

I want to find all of the records in the PRODUCTS table that either:

A) Don't match any records in the VENDORPRODUCTS table
or
B) If they DO match a record in VENDORPRODUCTS, the VPCOST field in
VENDORPRODUCTS is either 0 or NULL.

In these cases, the "match" criteria is that PRODUCTS.PRODBARCODE =
VENDORPRODUCTS.VPUPC and PRODUCTS.PRODSOURCE = VENDORPRODUCTS.VPVENDOR.

Basically, I'm trying to find all PRODUCTS that don't link to a nonzero cost.

Is there a better way to do this?

Thanks,
Joe