By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Please help me in choosing an SQL statement (inner, outer or left join)

100+
P: 222
Dear Sirs;


I'm trying to write SQL statement and it is not executing what I need.

tables are: customers(customerID, customerName)
Invoices(invoiceID,customerid,invoicedate,invoicea mount)
Receipt (receiptID,customerid,receiptdate,receiptamount)

using inner outer an left join is always making a combination on the query.

what i need is just to add to queries to each other for example:

invoiceID customerID invoicedate receiptamount receiptdate receiptID

22--------------171-----------1/1/2006-------------- 0 --------------0 -------------- 0
0----------------171--------------0-------------- -----120-----------1/2/2006---------21
0----------------171--------------0--------------------360-----------1/3/2006---------22
23---------------171----------1/4/2006--------------0 --------------- 0 ----------------0

The idea is to sort this query by date.

My relationship is connected table customers by invoices and customers by receipt (1 to infinity customerid to customerid(invoices and receipt))

I don't want any duplication in data.

does my relationship map me to this kind of query ?
Is there any feature that report in Access provide to such as queries?

Any help guys please please I read this articles many times
http://bytes.com/forum/thread608296.html and i don't think a single sql statement will help me

I WOULD BE MORE THAN HAPPY TO HEAR FROM YOU.




WASSIM S DACCACHE
Apr 27 '08 #1
Share this Question
Share on Google+
5 Replies


Delerna
Expert 100+
P: 1,134
try making a query for each of the tables.Each query will that take the fields that are in its table and adds fields that are not in its table. So each table will have that same fields that are in your result.

Then you can use a union query to join the results of the three queries into 1 recordset and sort them by date.

I can provide an example if you have trouble doing that.
Apr 27 '08 #2

100+
P: 222
Man;


Still have duplication in values.

Kindly, provide me more details or example.

Appreciate your help


WASSIM S DACCACHE
CCE
Apr 28 '08 #3

Delerna
Expert 100+
P: 1,134
actually you only need the two tables

Expand|Select|Wrap|Line Numbers
  1.  
  2. select invoiceID,
  3.         customerID,
  4.         invoicedate,
  5.         InvoiceAmount as Amount,
  6.         0 as receiptdate,
  7.         0 as receiptID
  8. from Invoices
  9. union all
  10. select 0 as invoiceID,
  11.         customerID,
  12.         0 as invoicedate,
  13.         receiptamount as Amount,
  14.         receiptdate,
  15.         receiptID
  16. from Receipt
  17.  
By the way
The reason you are getting duplicates with the joins is that there are 2 invoices and 2 receipts for a single customer which are probly for two seperate orders.
You have nothing to distinguish the two invoices from one another in your join, such as an order number. Ditto for the invoices.
so when you join the invoices to the receipts.
For each invoice there are 2 matching receipts, therefore the 2 invoices return 4 records.
I hope that I explained that clearly enough.
Apr 28 '08 #4

100+
P: 222
Dear Delerna,



I understood your logic. I appreciate you it works with me.


Thank you again for all your feedback and your aid.


Best regards;



WASSIM S DACCACHE
CCE
<< Link removed in Technical Thread >>
Apr 29 '08 #5

NeoPa
Expert Mod 15k+
P: 31,277
Wassim,

FYI The rules prohibit inclusion of links in the technical posts (except where they're part of the question).

I've removed the link and I'm sure it was done in innocense so no real problem.
Apr 30 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.