Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: need URGENT help with combining tables in SQL please


This question is answered.


Permlink Replies: 2 - Last Post: Jan 13, 2015 8:26 AM Last Post By: Eitan Arbel
Eitan Arbel

Posts: 508
Registered: 2/24/13
need URGENT help with combining tables in SQL please  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 10, 2015 6:40 AM
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

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: need URGENT help with combining tables in SQL please [Edit]
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 12, 2015 10:04 AM   in response to: Eitan Arbel in response to: Eitan Arbel
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 <customer fields>,
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
<join criteria>
where cb.cid is null
UNION
select <branch fields>,
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
<join criteria>
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)
Eitan Arbel

Posts: 508
Registered: 2/24/13
Re: need URGENT help with combining tables in SQL please [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 13, 2015 8:26 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank you VERY VERY MUCH Jeff !

you can't imagine how much you helped me, and how i appreciate it!
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02