SQL Statment

I have 2 Tabels , one showing the clinc data and oher there is amout of invoice I want to run report shwoing all records of clnc table and the total amount of invoice even null value during period time ... pls check my code which is correct but it showing only the records have figures and the clnc which have nill values it not appearing ... pls help us
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc  AND 
    b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc

Open in new window

ezaldeenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jwentingCommented:
It doesn't show those records because they don't have any child records.
The where clause excludes any records in clnc for which no records exist in invmaster (assuming no records exist in invmaster for which the clnc column is NULL, which would be a logical assumption).

You'd have to use something like a union on clnc to get records there with no child records as well.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database?
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a
LEFT JOIN invmaster b
  ON a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc

Open in new window

0
JonasMalmstenCommented:
You need to do an "outer left join", not sure which database you use, but syntax for oracle would be:

SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc(+)  AND
    (b.clnc is null or b.inv_date BETWEEN ? AND ?)
GROUP BY a.clnna, a.clnc
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

divyeshhdoshiCommented:
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc(+)  AND
    b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc
0
ezaldeenAuthor Commented:
Thanks for multi answers ...
This Access Database ....
I try to run SQl Statment which is given by JONAS & DIVYE.. but it not work fine
I use dataenvirment tool with VB6 ...
can any one give best statment work fine ..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ms access:
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
ezaldeenAuthor Commented:
Thanks ange....
It's work fine , but the field of (Total) showing NULL if there is no data , I need to show it as (0) Zero
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT a.clnna, a.clnc, ISNULL(SUM(b.total),0) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc
0
ezaldeenAuthor Commented:
Thanks
But still not solve it , it showing error , pls see the attachment
untitled.bmp
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry. ..
SELECT a.clnna, a.clnc, IF(ISNULL(SUM(b.total),0,SUM(b.total)) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
ezaldeenAuthor Commented:
Dear ...
I use Dataenvirment with VB6 ...
your statment to work with this >>> I try many times still there is a problem ... still error coming
the IF statment not work into select statment with dataenvirment in VB6 ... pls search another way
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry. ms access it's iif and not if in the query:
http://www.techonthenet.com/access/functions/advanced/iif.php
SELECT a.clnna, a.clnc, IIF(ISNULL(SUM(b.total),0,SUM(b.total)) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
ezaldeenAuthor Commented:
Thanks ...
I think this will work into access application but not with data envirment in VB6 ... I take it copy and paste on our vb6 application but not work and still there is an error
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I never worked with dataenvironment, so I don't know what the problem could be, at least not without seeing the EXACT ERROR !
remember, we are not sitting in front of your screen. I don't have any crystal balls ...
0
ezaldeenAuthor Commented:
Thanks Alot ...
I found this and it's work fine Now :
***********************************************************
SELECT a.clnna, a.clnc, SUM(IIf(isnull(b.total), 0, b.total))
    AS total
FROM clnc a LEFT OUTER JOIN
    invmaster b ON (a.clnc = b.clnc AND b.inv_date BETWEEN
    ? AND ?)
GROUP BY a.clnna, a.clnc
***********************************************************************
Thanks ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ezaldeenAuthor Commented:
fine
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.