Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Is it possible to show this query? |
Mon, Oct 20 2008 8:30 AM | Permanent Link |
=?iso-8859-1?Q?Santy_Concepci=F3n?= | I have two tables:
* One of them saves the list of sales, for example: NUMBER DATE TOTAL 1 2008-10-01 50 2 2008-10-03 25 3 2008-10-03 50 * Another table stores the payments methods of each sale (if any), for example NUMBER DATE TOTAL PAYMENT-METHOD 2 2008-10-03 10 CREDIT CARD 2 2008-10-05 15 BANK TRANSFER I would like to show ALL data in the following manner: NUMBER DATE TOTAL PAYMENTS 1 2008-10-01 50 0 2 2008-10-03 25 25 3 2008-10-03 50 0 If it is NOT possible, I would like to show a) only the records from table1 (sales) with no payments in table2 b) only the records from table1 (sales) with payments in table2 Is it possible? Thanks! |
Mon, Oct 20 2008 10:13 AM | Permanent Link |
"Eduardo [HPro]" | Santy
> SALES_TABLE > NUMBER DATE TOTAL > 1 2008-10-01 50 > 2 2008-10-03 25 > 3 2008-10-03 50 > > PAYMENT_TABLE > NUMBER DATE TOTAL PAYMENT-METHOD > 2 2008-10-03 10 CREDIT CARD > 2 2008-10-05 15 BANK TRANSFER > > I would like to show ALL data in the following manner: > > NUMBER DATE TOTAL PAYMENTS > 1 2008-10-01 50 0 > 2 2008-10-03 25 25 > 3 2008-10-03 50 0 select number, sales_table.date, sales_table.total, sum(total) from payment_table inner join sales_table on (payment_table.number = sales_table.number) group by number Eduardo |
Mon, Oct 20 2008 1:19 PM | Permanent Link |
=?iso-8859-1?Q?Luis_Concepci=F3n?= | Thanks for your answer, but I'm afraid I was wrong with my explanation (or
maybe I misunderstood what I really needed). Actually, sales table has its own payment method too, so the problem is a little bit difficult. Some sales are payed with a UNIQUE payment method (not saved in payments table), but another ones are payed with two (or more) payments methods (saves in payments table) So we have: SALES_TABLE NUMBER DATE TOTAL PAYMENT-METHOD 1 2008-10-01 50 CASH 2 2008-10-03 25 {SEVERAL METHODS} 3 2008-10-03 50 CREDIT CARD PAYMENT_TABLE NUMBER DATE TOTAL PAYMENT-METHOD 2 2008-10-03 10 CREDIT CARD 2 2008-10-05 15 BANK TRANSFER I need to show all sales WITH UNIQUE payments methods, and all sales WITH SEVERAL payments methods. Don't matter if it is shown in one simple query or separate ones. For example (if possible with one simple query) NUMBER DATE TOTAL PAYMENTS METHOD 1 2008-10-01 50 0 CASH 2 2008-10-03 25 10 CREDIT CARD 2 2008-10-05 25 15 BANK TRANSFER 3 2008-10-03 50 0 CREDIT CARD BUT! If it is better to get it with two different queries, no problem: Show all sales with UNIQUE payments: NUMBER DATE TOTAL METHOD 1 2008-10-01 50 CASH 3 2008-10-03 50 CREDIT CARD Show all sales with various payment methods: NUMBER DATE TOTAL METHOD 2 2008-10-01 10 CREDIT CARD 2 2008-10-05 15 BANK TRANSFER I have been trying UNION, INTERACT, etc... queries with no success. Thanks for your patience and please forgive my previous explanation. "Eduardo [HPro]" <contato@hpro.com.br> escribió en el mensaje de noticias:F606080C-9A2F-450C-A6B7-B7DCD5FCC3CA@news.elevatesoft.com... > Santy > >> SALES_TABLE >> NUMBER DATE TOTAL >> 1 2008-10-01 50 >> 2 2008-10-03 25 >> 3 2008-10-03 50 >> >> PAYMENT_TABLE >> NUMBER DATE TOTAL PAYMENT-METHOD >> 2 2008-10-03 10 CREDIT CARD >> 2 2008-10-05 15 BANK TRANSFER >> >> I would like to show ALL data in the following manner: >> >> NUMBER DATE TOTAL PAYMENTS >> 1 2008-10-01 50 0 >> 2 2008-10-03 25 25 >> 3 2008-10-03 50 0 > > select number, sales_table.date, sales_table.total, sum(total) > from payment_table > inner join sales_table on (payment_table.number = sales_table.number) > group by number > > Eduardo > |
Mon, Oct 20 2008 2:43 PM | Permanent Link |
"Eduardo [HPro]" | Santy
Well, if I understand it correctly then > SALES_TABLE > NUMBER DATE TOTAL > 1 2008-10-01 50 > 2 2008-10-03 25 > 3 2008-10-03 50 > > PAYMENT_TABLE > NUMBER DATE TOTAL PAYMENT-METHOD > 2 2008-10-03 10 CREDIT CARD > 2 2008-10-05 15 BANK TRANSFER > > I would like to show ALL data in the following manner: > > NUMBER DATE TOTAL PAYMENTS > 1 2008-10-01 50 0 > 2 2008-10-03 25 25 > 3 2008-10-03 50 0 select number, sales_table.date, sales_table.total, sum(total), 'Other' from payment_table inner join sales_table on (payment_table.number = sales_table.number) union all select number, date, total, total, payment_method from sales_table where payment_method in ('Cash','Credit card','Other method') group by number But I will suggest you to add at least one record to payment table. It will make easier this kind of query. Eduardo |
Tue, Oct 21 2008 5:09 AM | Permanent Link |
"John Hay" | >
> SALES_TABLE > NUMBER DATE TOTAL PAYMENT-METHOD > 1 2008-10-01 50 CASH > 2 2008-10-03 25 {SEVERAL METHODS} > 3 2008-10-03 50 CREDIT CARD > > PAYMENT_TABLE > NUMBER DATE TOTAL PAYMENT-METHOD > 2 2008-10-03 10 CREDIT CARD > 2 2008-10-05 15 BANK TRANSFER > > > I need to show all sales WITH UNIQUE payments methods, and all sales WITH > SEVERAL payments methods. > Don't matter if it is shown in one simple query or separate ones. > > For example (if possible with one simple query) > > NUMBER DATE TOTAL PAYMENTS METHOD > 1 2008-10-01 50 0 CASH > 2 2008-10-03 25 10 CREDIT CARD > 2 2008-10-05 25 15 BANK TRANSFER > 3 2008-10-03 50 0 CREDIT CARD If this is the result you want (ie separate lines for each payment method) then try SELECT number,date,total,0 as payments,payment_method FROM sales_table WHERE number not in (SELECT number FROM payment_table) UNION ALL SELECT number,date,sales_table.total,payment_table.total as payments,payment_table.payment_method FROM payment_table JOIN sales_table ON payment_table.number=sales_table.number John |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |