We help IT Professionals succeed at work.

Problem with SQL

javiertb
javiertb asked
on
Medium Priority
217 Views
Last Modified: 2010-05-18
I know this is more concerned with database than with Delphi but as I'm using it in a delphi program here it goes:

I have one table with two non required fields which reference two other tables.
SELECT invoice.number, client.name, creditcard.description FROM invoice, client, creditcard
WHERE invoice.client=client.number AND invoice.ccard=creditcard.number

the problem is that I only get the rows which invoice.client and invoice.ccard are not null, and I want to get these ones too with an empty description or name fields from the ccard/client tables.

I've tried with a LEFT JOIN and I got those ones too, but ccard.description and client.name where not empty (as I want and it should be) but with wrong values.

Thanks in advance
Comment
Watch Question

Commented:
Why don't you assign a defult value to the fields like -1 or "UNKNOWN" and adjust you'r SQL ?????

Author

Commented:
I already thought about that but I don't want to have a default record in the tables creditcard and clients and also, I don't want these default values to apear in the dbgrid where I show the invoice number, client number, client name, creditcard number and credit card description.

Author

Commented:
I already thought about that but I don't want to have a default record in the tables creditcard and clients and also, I don't want these default values to apear in the dbgrid where I show the invoice number, client number, client name, creditcard number and credit card description.

Commented:
Make sure that you don't save any unwanted values in the non
required fields. This may be the case if you do not initialize
all the parameters of an insert stored procedure.
---
Your problem is a good example of the usefullness
of the STATE NULL of an SQL-Database (InterBase ?). If you
do not know the value of a field a NULL indicates this
state. If you put a default value in it's another issue..
333

Commented:
Hi,
try this:
  SELECT invoice.number, client.name, creditcard.description FROM invoice, client, creditcard
  WHERE nvl(invoice.client,0)=client.number AND nvl(invoice.ccard,0)=creditcard.number

Function NVL(e1,e2) returns e2, if e1 is NULL. So ,in this case, when invoice.client or invoice.ccard are NULL, the function returs 0 and not NULL. You can change 0 to what you need.

Author

Commented:
NVL function is not supported by the SQL in Delphi.
Right now, I'm not using Interbase, stored procedures and so on (in a future, maybe). I'm just using SQL to query a paradox table.

Commented:
As I understand from your description
you query ALL rows from client and credircard
(ccard.description and client.name not empty) and
only those rows from invoice that satisfy the join condition.

But you want quite the contrary:
query ALL rows from invoice and only those rows from client and creditcard
that satisfy the join condition.

Syntax depends on database which you use.
For example under Oracle I would write as
SELECT invoice.number, client.name, creditcard.description
FROM invoice, client, creditcard
WHERE invoice.client=client.number(+) AND invoice.ccard=creditcard.number(+)

I do not remember what "LEFT JOIN" means, but try "RIGHT JOIN"

Author

Commented:
I already tried RIGHT JOIN, but it's not what I'm looking for.
If I use RIGHT JOIN I get all the records from the right part of the expresion in this case client and ccard.
333

Commented:
Sorry, I have tested my code on Oracle DB only.
But this works with Paradox (if I understand your question):

SELECT invoice.number, client.name, creditcard.description FROM invoice, client, creditcard
         WHERE (invoice.client=client.number AND invoice.ccard=creditcard.number) OR (invoice.client is null OR invoice.ccard is null)

Commented:
Try it, I test it on Paradox tables

SELECT invoice.number, client.name, creditcard.description
FROM
  invoice LEFT JOIN client ON invoice.client=client.number
          LEFT JOIN creditcard ON invoice.ccard=creditcard.number

333,
Your query produce cartesian product.
(when invoice.client is null or invoice.ccard is null
query combine this row with EACH row from client and creditcard)

Commented:
Javiertb, Did you solve problem?

Author

Commented:
No, I still haven't solved the problem. The point is how can I avoid this cartesian product when client or ccard are NULL and getting an empty field instead?

Commented:
Are you try my comment?
SELECT invoice.number, client.name, creditcard.description
FROM
  invoice LEFT JOIN client ON invoice.client=client.number
          LEFT JOIN creditcard ON invoice.ccard=creditcard.number

I test it and it works.
client.name was empty when invoice.client was NULL and
creditcard.description was empty when invoice.ccard was NULL

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.