Solved

Problem with SQL

Posted on 1998-08-11
14
181 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
0
Comment
Question by:javiertb
  • 5
  • 4
  • 2
  • +3
14 Comments
 

Expert Comment

by:dotan
ID: 1361893
Why don't you assign a defult value to the fields like -1 or "UNKNOWN" and adjust you'r SQL ?????
0
 
LVL 2

Author Comment

by:javiertb
ID: 1361894
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.
0
 
LVL 2

Author Comment

by:javiertb
ID: 1361895
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.
0
 
LVL 1

Expert Comment

by:EmmDieh
ID: 1361896
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..
0
 
LVL 2

Expert Comment

by:333
ID: 1361897
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.
0
 
LVL 2

Author Comment

by:javiertb
ID: 1361898
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.

0
 
LVL 3

Expert Comment

by:vladika
ID: 1361899
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"

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:javiertb
ID: 1361900
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.
0
 
LVL 2

Expert Comment

by:333
ID: 1361901
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)
0
 
LVL 3

Expert Comment

by:vladika
ID: 1361902
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)

0
 
LVL 3

Expert Comment

by:vladika
ID: 1361903
Javiertb, Did you solve problem?

0
 
LVL 2

Author Comment

by:javiertb
ID: 1361904
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?
0
 
LVL 3

Expert Comment

by:vladika
ID: 1361905
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

0
 

Accepted Solution

by:
DPedrelli earned 100 total points
ID: 1361906
Ideally, you should avoid forcing the join type if possible.  That way your server should optimize the join, if you force the join type you loose that optimization.  I don't know what you are using for a backend so implementation may vary slightly (this is MSSQL Server) but basically this should do what you want.  To make ccard.description and client.name null, include a CASE clause in your select satement.
SELECT
"NAME" =
  CASE
  WHEN (invoice.client IS NULL) or (invoice.ccard IS NULL) THEN
    NULL
  ELSE
    client.name
  END,
"DESCRIPTION" =
  CASE
  WHEN (invoice.client IS NULL) or (invoice.ccard IS NULL) THEN
    NULL
  ELSE
    ccard.description
  END,
.
FROM
.
JOIN
.
ON
.
To allow all based on join condition or where invoice.client and/or invoice.ccard are NULL.
Try these for you ON clauses (or WHERE if you do it that way):
(invoice.client=client.number or invoice.client IS NULL) (invoice.ccard=creditcard.number or invoice.ccard IS NULL)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
proper way to parse url in delphi 2 163
Convert a string into a TDateTime 5 50
Performance of SQL statement 37 100
JAudiorecorder record freezing the app 29 60
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now