• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Anyone any good at SQL?

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I know this forum is for Java but I'm a java developer that has to figure this SQL out and I have done Joins until I'm blue to no avail.
With the following table, key and relationship information:
Client: nClientId, cClientName where nClientId is the PK.
Terms: nTermsId, cTermsDesc where nTermsId is the PK.
Bills: nBillId, nClientID, nTermsId, nAmount where nBillId is the PK and nClientId
is an FK to the Client Table.
Payments: nBillId, dDate, nPaid where nBillId is an FK into bills.
For every client there exists none, one or many bills.
For every bill there exists none, one, or many payments.
For every bill there exists one term.
We are interested in seeing the following columns for all clients. For each client, we
wish to see all bills - paid or not:
cClientName, nBillId, cTermsDesc, dDate & nPaid
Anyone? I would appreciate it dearly!
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Annie Hampton:
For each client, we wish to see all bills - paid or not:


That's the key statement driving your query. You want to outer join the bills and payment tables such that each row from bills is preserved in the result set even if no corresponding row exists on payments (then inner join client to bills and terms to bills). There are several flavors of outer join syntax. What product are you using?
For example, Oracle uses (+) for outer joins. Other products use LEFT JOIN and RIGHT JOIN.
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try this:
SELECT Client.ClientName, Bills.BillId, Terms.TermsDescription, Payments.Date, Payments.Paid
FROM (Terms INNER JOIN (Client INNER JOIN Bills ON Client.ClientId = Bills.ClientId) ON Terms.TermsId = Bills.TermsId) INNER JOIN Payments ON Bills.BillId = Payments.BillId;
I changed the names to something less intricate, and made Payments.BillId the primary key in the Payments table, since only a primary key in one table can be a foreign key in another table.
I didn't write this query, SQL server did it after I created all the tables and set the relationships.
Unless you are faced with a pre-existing database, that you can't change, I would seriously consider making paid a field in the Bills table and not in the payments table, since a payment is paid by definition. You also might want to change the field names to something without built-in prefixes, since over time remembering the extra lower-case letters becomes tiresome.
Good luck,
Edward Brode
[ May 15, 2002: Message edited by: Edward Brode ]
[ May 15, 2002: Message edited by: Edward Brode ]
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The outermost join needs to be a LEFT JOIN to satisfy the requirements given.
SELECT Client.ClientName, Bills.BillId, Terms.TermsDescription, Payments.Date, Payments.Paid
FROM (Terms INNER JOIN (Client INNER JOIN Bills ON Client.ClientId = Bills.ClientId) ON Terms.TermsId = Bills.TermsId) LEFT JOIN Payments ON Bills.BillId = Payments.BillId;
[ May 15, 2002: Message edited by: Michael Matola ]
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also, is "paid" an amount or a yes/no flag? If it's a flag, it's probably redundant, the existence/nonexistence of a date paid being the better indicator.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are using Oracle, the following statement may do the job.
Select A.clientname, C.nBillid,B.nTermDesc, D.ndate,D.nPaid
from Client A, Terms B, Bills C, Payments D
where
A.nClientID(+)=C.nClientID and
B.nTermId=C.nTermId and
C.nBillId=D.nBillId(+)
order by A.nClientId
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks everybody for the answers. I sort of got frustrated with it and forgot about it for a couple of weeks. However, I still need some clarification. First of all, I'm using SQL Server or else I think the above post would solve my problem.
We need to return all clients, whether they have bills or not. That is the "gotcha" that is confusing me.
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In that case, either of the following queries should be correct.
SELECT c.clientName, b.billId, t.termsDescription, b.amount, p.date, p.paid
FROM ( ( bills AS b LEFT JOIN terms AS t ON b.termsId = t.termsId )
LEFT JOIN payments AS p ON b.billId = p.billId )
RIGHT JOIN client AS c ON c.clientId = b.clientId;

SELECT c.clientName, b.billId, t.termsDescription, b.amount, p.date, p.paid
FROM ( ( client AS c LEFT JOIN bills AS b ON c.clientId = b.clientId )
LEFT JOIN payments AS p ON b.billId = p.billId )
LEFT JOIN terms AS t ON b.termsId = t.termsId;
Note: I wrote these using Microsoft Access, not SQL Server. Originally in the first query I wanted the innermost join to be an inner join, but I got a error about the join not being supported. Anyhow, the second query might be a little easier to understand since it's just a cascade of left joins.
 
She's out of the country right now, toppling an unauthorized dictatorship. Please leave a message with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic