Hi,

I want to extract all fields from a Customers table where a CustNo is say
"2".  However, I would also like to add a boolean column to the results
table that shows true if the customer has any records in Orders table and
false if the customer has no records in Orders table.  A customer can have
many orders, but I want one record per customer only.

It sounds very simple and I managed to get my results (well almost -
True/False reversed) using:

SELECT DISTINCT *, (o.CustNo IS NULL) AS "NoOrders"
FROM Customers
LEFT OUTER JOIN Orders o ON (o.CustNo = Customers.CustNo)
WHERE CustNo=2

The problem is that the Customers table contains a Blob field and this
doesn't work with the DISTINCT query parameter - I want the complete record
including the Blob field in my results table.

Any suggestions how I can achieve this (in a single SQL statement if
possible)?

Thanks,
Jack

Jack


I'm sure there's a more elegant way but I came up with:

SELECT DISTINCT _ID, FALSE AS NoOrders  INTO Memory\T1 FROM Companies WHERE _Status = 'Client';
SELECT _fkCompanies INTO Memory\T2 FROM Calls
JOIN Companies ON Companies._ID = Calls._fkCompanies
WHERE _fkCompanies IS NOT NULL;
DELETE FROM Memory\T2 WHERE NOT _fkCompanies IN (SELECT _ID FROM Memory\T1);
UPDATE Memory\T1 SET NoOrders = TRUE WHERE _ID IN (SELECT _fkCompanies FROM Memory\T2);
SELECT * FROM Companies JOIN "Memory\T1" X ON _ID = X._ID;


Roy Lambert

"Jack Marsh" <jack@marshdata.co.uk> wrote in
news:D252BFD8-AE29-4427-8C1F-A1E107B70EBF@news.elevatesoft.com:

> Hi,
>
> I want to extract all fields from a Customers table where a CustNo is
> say "2".  However, I would also like to add a boolean column to the
> results table that shows true if the customer has any records in
> Orders table and false if the customer has no records in Orders table.
>  A customer can have many orders, but I want one record per customer
> only.
>
> It sounds very simple and I managed to get my results (well almost -
> True/False reversed) using:
>
> SELECT DISTINCT *, (o.CustNo IS NULL) AS "NoOrders"
> FROM Customers
> LEFT OUTER JOIN Orders o ON (o.CustNo = Customers.CustNo)
> WHERE CustNo=2
>
> Any suggestions how I can achieve this (in a single SQL statement if
> possible)?

What about:

SELECT C.*, MAX(o.CustNo) > 0 AS WithOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON (o.CustNo = C.CustNo)
WHERE C.CustNo=2
GROUP BY C.CustNo
ORDER BY C.CustNo

--
Chris

Thanks for your help guys - I'll give this a try!

Regards,
Jack

"Jack Marsh" <jack@marshdata.co.uk> wrote in message
news:D252BFD8-AE29-4427-8C1F-A1E107B70EBF@news.elevatesoft.com...
> Hi,
>
> I want to extract all fields from a Customers table where a CustNo is say
> "2".  However, I would also like to add a boolean column to the results
> table that shows true if the customer has any records in Orders table and
> false if the customer has no records in Orders table.  A customer can have
> many orders, but I want one record per customer only.
>
> It sounds very simple and I managed to get my results (well almost -
> True/False reversed) using:
>
> SELECT DISTINCT *, (o.CustNo IS NULL) AS "NoOrders"
> FROM Customers
> LEFT OUTER JOIN Orders o ON (o.CustNo = Customers.CustNo)
> WHERE CustNo=2
>
> The problem is that the Customers table contains a Blob field and this
> doesn't work with the DISTINCT query parameter - I want the complete
> record including the Blob field in my results table.
>
> Any suggestions how I can achieve this (in a single SQL statement if
> possible)?
>
> Thanks,
> Jack
>