hi all,.

i have a very big problem creating the right SQL, and please i REALLY need help on this.

i have 3 table :

1. "Customers" C
C.cid
C.eid
C.chptz
C."CustomerName"
C."CustomerAddress"
C."CustomerCity"
C."CustomerCell1"
C."CType"

2. "CustomerBranches" B
B.cid
B.cbid
B.eid
B.chptz
B."CustomerName"
B."BranchName"
B."BranchAddress"
B."BranchCity"
B."ContactManCell"

3. "Invoices" I
I.iid
I.cid
I.eid
I.cbid
I.chptz
I."PaymentDate"
I."InvoiceSum"



i need a result that will show me all the Customers and their Branches and if they owe money or not.
owing money is according to the I."PaymentDate" filed - if it's empty(or null) or not
if a Customer or a branch of the customer owes money, then the field should show the word "Yes".

some Customers don't have branches, but i need to show them too.
if the Customer have a branch, then i don't need to see the customer's information, but it's branches information.

the reason why there is C."CustomerName" and B."CustomerName", is because sometimes i need to show info only from the "Customers" table, and sometimes only from the "CustomerBranches" table, so i thought it would be faster for the SQL to just get it from 1 table.


i've been trying to get the right SQL for 3 days now, but i couldn't do it...

i would REALLY appreciate you help.
(sorry for my broken english)
Thank you

Thank you VERY VERY MUCH Jeff !

you can't imagine how much you helped me, and how i appreciate it!

Eitan Arbel wrote:
> hi all,.
> 
> i have a very big problem creating the right SQL, and please i REALLY need help on this.
> 
> i have 3 table :
> 
> 1. "Customers" C
> C.cid
> C.eid
> C.chptz
> C."CustomerName"
> C."CustomerAddress"
> C."CustomerCity"
> C."CustomerCell1"
> C."CType"
> 
> 2. "CustomerBranches" B
> B.cid
> B.cbid
> B.eid
> B.chptz
> B."CustomerName"
> B."BranchName"
> B."BranchAddress"
> B."BranchCity"
> B."ContactManCell"
> 
> 3. "Invoices" I
> I.iid
> I.cid
> I.eid
> I.cbid
> I.chptz
> I."PaymentDate"
> I."InvoiceSum"
> 
> 
> 
> i need a result that will show me all the Customers and their Branches and if they owe money or not.
> owing money is according to the I."PaymentDate" filed - if it's empty(or null) or not
> if a Customer or a branch of the customer owes money, then the field should show the word "Yes".
> 
> some Customers don't have branches, but i need to show them too.
> if the Customer have a branch, then i don't need to see the customer's information, but it's branches information.
> 
> the reason why there is C."CustomerName" and B."CustomerName", is because sometimes i need to show info only from the "Customers" table, and sometimes only from the "CustomerBranches" table, so i thought it would be faster for the SQL to just get it from 1 table.
> 
> 
> i've been trying to get the right SQL for 3 days now, but i couldn't do it...
> 
> i would REALLY appreciate you help.
> (sorry for my broken english)
> Thank you

Personally I would do this as a selectable stored procedure and solve it in 
parts.  As a Single SQL you would use a UNION.

something like

select ,
        Cast(Coalesce((Select distinct 'Yes' from Invoices i where i.cid = c.cid 
and i.paymentDate is not null), 'No') as VarChar(3))
   from Customers c left outer join CustomerBranches cb on
        
  where cb.cid is null
UNION
select ,
        Cast(Coalesce((Select distinct 'Yes' from Invoices i where i.cid = 
cb.cid and i.paymentDate is not null), 'No') as VarChar(3))
   from Customers c left outer join CustomerBranches cb on
        
  where cb.cid is not null

The fields must be the same type and order for the upper and lower halves of the 
SQL.

-- 
Jeff Overcash (TeamB)
       (Please do not email me directly unless  asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted  corridors of Whitehall.
              (Fish)