Board index » delphi » Am I using SQL right ?

Am I using SQL right ?

In Delphi 3 with level 7 Paradox tables I am trying to enter search
criteria in one table then look for matches in another. I'm trying to
match prospective purchasers on a register to vehciles on a register and
visa versa. Using the following query produced for me by Database
desktop I don't get any result if the any search fields are empty.

SELECT DISTINCT D.Linker, D.Make, D.Model, D.Wheelbase, D.Body,
D."Year", D.Mot, D.Tax, D.Mileage, D.DateOn, D.Price, D.MinPrice,
D.Terms, D.Authority, D.Engine, D.Fuel, D.Drive
FROM "vehicle.DB" D, "buyer.DB" D1
WHERE
(D1.Make = D.Make)
 AND (D1.Model = D.Model)
 AND (D1.Type = D.Wheelbase)
 AND (D1.Body = D.Body)
 AND (D1.Maxprice >= D.MinPrice)
 AND (D1.Engine = D.Engine)
 AND (D1.Fuel = D.Fuel)
 AND (D1.Drive = D.Drive)
ORDER BY D.Linker, D.Make, D.Model, D.Wheelbase, D.Body, D."Year",
D.Mot, D.Tax, D.Mileage, D.DateOn, D.Price, D.MinPrice, D.Terms,
D.Authority, D.Engine, D.Fuel, D.Drive

How can I trap for empty fields in table 'D1' ?

Regards, Ian
From The Royal Forest of Dean, Gloucestershire, UK hence
    'FOREST 4 x 4 CENTRE' for LANDROVERS (www.fourx4.demon.co.uk)
    'FOREST WEB' for WEB DESIGN (www.demon.co.uk/fweb)
    'FOREST SOFTWARE' for BUSINESS SOLUTIONS        
Phone +44(0)1594 822606, Mobile(0)402 000132, Fax +44(0)1594 822634

 

Re:Am I using SQL right ?


How about:

  WHERE ((D1.Make = D.Make) or (D1.Make = ""))  etc.

Ed

-----
Ed Hochman - MBH Systems - e...@mbhsys.com

Re:Am I using SQL right ?


Try...

WHERE
((D1.Make = D.Make) or (D.Make = NULL))
 AND ((D1.Model = D.Model) OR (D.Model = NULL))
 ...
 ...

Quote
Ian Robinson <I...@fourx4.demon.co.uk> writes:
>In Delphi 3 with level 7 Paradox tables I am trying to enter search
>criteria in one table then look for matches in another. I'm trying to
>match prospective purchasers on a register to vehciles on a register and
>visa versa. Using the following query produced for me by Database
>desktop I don't get any result if the any search fields are empty.
>SELECT DISTINCT D.Linker, D.Make, D.Model, D.Wheelbase, D.Body,
>D."Year", D.Mot, D.Tax, D.Mileage, D.DateOn, D.Price, D.MinPrice,
>D.Terms, D.Authority, D.Engine, D.Fuel, D.Drive
>FROM "vehicle.DB" D, "buyer.DB" D1
>WHERE
>(D1.Make = D.Make)
> AND (D1.Model = D.Model)
> AND (D1.Type = D.Wheelbase)
> AND (D1.Body = D.Body)
> AND (D1.Maxprice >= D.MinPrice)
> AND (D1.Engine = D.Engine)
> AND (D1.Fuel = D.Fuel)
> AND (D1.Drive = D.Drive)
>ORDER BY D.Linker, D.Make, D.Model, D.Wheelbase, D.Body, D."Year",
>D.Mot, D.Tax, D.Mileage, D.DateOn, D.Price, D.MinPrice, D.Terms,
>D.Authority, D.Engine, D.Fuel, D.Drive
>How can I trap for empty fields in table 'D1' ?
>Regards, Ian
>From The Royal Forest of Dean, Gloucestershire, UK hence
>    'FOREST 4 x 4 CENTRE' for LANDROVERS (www.fourx4.demon.co.uk)
>    'FOREST WEB' for WEB DESIGN (www.demon.co.uk/fweb)
>    'FOREST SOFTWARE' for BUSINESS SOLUTIONS        
>Phone +44(0)1594 822606, Mobile(0)402 000132, Fax +44(0)1594 822634

--
Luke Webber

* Note: The opinions expressed by Luke Webber are in no way supported *
*       by his employers, Luke Webber Consulting Services             *

Re:Am I using SQL right ?


In article <luke.871510899@oberon>, Luke Webber <l...@oberon.sub.net.au>
writes

Quote
>Try...

>WHERE
>((D1.Make = D.Make) or (D.Make = NULL))
> AND ((D1.Model = D.Model) OR (D.Model = NULL))
> ...

Thanks for that, Luke.

I think that would work if I were expecting any blank fields in the
'target' table but my problem is that I have a say 8 fields in the
'buyers' table some of which may be left empty to create a 'wide' search
of the 'seller's' table.

The seller's table has *all* the details of the car but the buyer may
want to select all of a certian make under a certain price i.e. only two
fields. I *think* what I want is something like:

if (D.Make > '') then (D1.Make = D.Make)

and so on for the rest of the fields. The WHERE needs to be conditional
on there being a value.

Any ideas please.

Regards, Ian
From The Royal Forest of Dean, Gloucestershire, UK hence
    'FOREST 4 x 4 CENTRE' for LANDROVERS (www.fourx4.demon.co.uk)
    'FOREST WEB' for WEB DESIGN (www.demon.co.uk/fweb)
    'FOREST SOFTWARE' for BUSINESS SOLUTIONS        
Phone +44(0)1594 822606, Mobile(0)402 000132, Fax +44(0)1594 822634

Re:Am I using SQL right ?


Quote
Ian Robinson wrote:

> In Delphi 3 with level 7 Paradox tables I am trying to enter search
> criteria in one table then look for matches in another. I'm trying to
> match prospective purchasers on a register to vehciles on a register and
> visa versa. Using the following query produced for me by Database
> desktop I don't get any result if the any search fields are empty.

> [SNIP]

> How can I trap for empty fields in table 'D1' ?

Hi,
In Delphi 1.02 i use outer joins to do that.
You will find good examples at page 120-122 of "Local Interbase
Server Manual".
Example:
"Table1 LEFT OUTER JOIN Table2 ON table1.Fieldname=Table2.Fieldname":
This statement extracts all the rows of table1 and the matching
rows of table2. If there is no matching row(s) in Table2, fields
from table2 are left blank.
In addition, using outer joins is faster than using the classical
scheme "select xxx from yyy where zzz".
The reason is that "select xxx from yyy where zzz" will build a
temporary table
with all the rows of table1 times all the rows of table2, and then will
decimate that table by applying the "where" conditions.
With left outer join instead, the temporary table will contain
only the rows of table1 plus the matching rows (if any) of table 2.

I am sure that this method works well also with D3.

Alberto Braggiotti
bragg...@ladseb.pd.cnr.it

Other Threads