combining four sql statements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
abushahin
Forum Commoner
Posts: 42
Joined: Wed Nov 25, 2009 12:35 pm
Location: london

combining four sql statements

Post by abushahin »

hey im not the best at queries but i was wondering if someone could point to a way of combining these queries that i have together.
no.1

Code: Select all

SELECT amount, notes FROM credit WHERE customer_Id = $cust_id
no.1

Code: Select all

SELECT amount, notes FROM payment WHERE customer_Id = $cust_id
no.3

Code: Select all

SELECT sum( amount )  AS totalpaid FROM payment WHERE customer_Id = $cust_id
no.4

Code: Select all

SELECT sum( amount )  AS totaltaken FROM credit WHERE customer_Id = $cust_id
id like to be able to retrieve all this data together as one query any help appreciated thanks abu
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: combining four sql statements

Post by iankent »

You can't do it as one query without a lot of redundant data, but you can do it with two.

First off, you'd probably be better reorganising your tables so that for each 'transaction' there's a 'credit' and 'debit' (I'm assuming your payment and credit tables are opposites of each other, despite credit and payment being the same!). This means you can easily sum the amount debited and credited using a single query, e.g.

Code: Select all

SELECT SUM(credit) AS credited, SUM(debit) AS debited FROM tbl_transactions WHERE customer_id=123;
But, to answer your question, you can join the first two queries using a UNION, but you'll need an additional column to identify whether its payment or credit, or alternatively invert one of the numbers (i.e., a negative value indicates a debit), so:

Code: Select all

SELECT amount, notes, 'credit' as source FROM credit WHERE customer_Id = $cust_id UNION SELECT amount, notes, 'payment' as source FROM payment WHERE customer_Id = $cust_id;
Similarly you could UNION together the last two queries (return two rows) or as subqueries (single row but two columns), but I'm not really sure there'd be any benefit to doing so as two queries are still being carried out.
abushahin
Forum Commoner
Posts: 42
Joined: Wed Nov 25, 2009 12:35 pm
Location: london

Re: combining four sql statements

Post by abushahin »

hey thanks for your reply, ok the second query is fab and is just what i needed! about the first query you reckon i should have the credit and payment table merged right? the only reason i didnt do that is because credit is taken at one date and payments are made on other dates that would mean on an entry of a credit or payment one of the fields would be empty.
i need specifically these two summed separately because it works out how much is being currently owed by minusing one form other. anyway the query for that didnt work as it is summing the entire table despite putting customer_Id as the condition.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: combining four sql statements

Post by iankent »

abushahin wrote:about the first query you reckon i should have the credit and payment table merged right? the only reason i didnt do that is because credit is taken at one date and payments are made on other dates that would mean on an entry of a credit or payment one of the fields would be empty.
I personally think that would make more sense yes, a table for transactions where each entry contains both a credit and a debit. Set the irrelevant value to 0. That way, you can do a SUM() on both columns at once to get the total credits and debits in a single query and result. And you could work out how much is owed in MySQL by creating a third column, e.g.:

Code: Select all

SELECT SUM(credit) AS credited, SUM(debit) AS debited, SUM(credit)-SUM(debit) AS balance FROM transactions WHERE customer_id=123;
(Not sure if my credited - debited bit is the right way around :p)
abushahin wrote:anyway the query for that didnt work as it is summing the entire table despite putting customer_Id as the condition.
not sure what you mean? which query?
abushahin
Forum Commoner
Posts: 42
Joined: Wed Nov 25, 2009 12:35 pm
Location: london

Re: combining four sql statements

Post by abushahin »

iankent wrote: not sure what you mean? which query?
oh sorry i meant the first query you wrote but by looking again i realised it wouldve been after a redoing the table, my bad! :oops:
neway i may just do it like you said combining the two tables together it makes more sense, all that i learnt bout 3NF out the door :lol: regards abu
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: combining four sql statements

Post by iankent »

abushahin wrote:
iankent wrote: not sure what you mean? which query?
neway i may just do it like you said combining the two tables together it makes more sense, all that i learnt bout 3NF out the door :lol: regards abu
3NF is generally just a guideline, albeit a very good one. sometimes its better not to normalise too far because you start making things worse instead of better. in your two table structure you have to run four queries against two tables to get the numbers you need (even if you UNION them, which in fact could make it worse!). This becomes a bigger problem if you want to display a credit/debit history to the users as you have to UNION the statements and then sort them.

with a single combined table you can get it all in two queries, and MySQL only has to look in the index for one table, and to display a debit/credit history, its just a single query with a simple sort! depending on the size of your tables you may want to consider indexing the credit and debit columns for faster results.

also, a single transaction could include both a credit and debit, so even to 3NF you don't need to separate it as there's not really any redundant data
Post Reply