-
Mar 27th, 2018, 01:23 PM
#1
Thread Starter
New Member
[RESOLVED] Need Help in MYSQL Query
Hello,
I have the below query, which gives me correct result if there is any product in daily sale, but if there is no product and only payments on customer_payments tables it show blank report. plz help
Code:
select `customer`.`ID`, `customer`.`full_name`,
`customer`.`shop_name`, `customer`.`address`,
`customer`.`city`, `customer`.`phone`, `customer`.`mobile`,
`customer`.`cDate`,
`customer_payments`.`ID` as `customer_payments_ID`,
`customer_payments`.`customer_id`,
`customer_payments`.`billno`,
`customer_payments`.`cDate` as `customer_payments_cDate`,
`customer_payments`.`bill_amount`,
`customer_payments`.`paid_amount`,
`customer_payments`.`discount`,
`customer_payments`.`ret_amount`,
`customer_payments`.`balance`,
`customer_payments`.`description`,
`dailysale`.`ID` as `dailysale_ID`, `dailysale`.`payment_id`,
`dailysale`.`product_id`, `dailysale`.`qty`,
`dailysale`.`price`, `dailysale`.`total`,
`dailysale`.`description` as `dailysale_description`,
`product`.`ID` as `product_ID`, `product`.`product_name`
from (((`customer` `customer`
left
join `customer_payments` `customer_payments`
on (`customer_payments`.`customer_id` = `customer`.`ID`))
inner join `dailysale` `dailysale`
on (`dailysale`.`payment_id` = `customer_payments`.`ID`))
left
join `product` `product`
on (`product`.`ID` = `dailysale`.`product_id`))
where not (`dailysale`.`product_id` is null)
-
Mar 27th, 2018, 01:33 PM
#2
Re: Need Help in MYSQL Query
Moved to database development.
-
Mar 27th, 2018, 01:44 PM
#3
Re: Need Help in MYSQL Query
Probably your inner join.
Try changing to a left join
Why do you have single quotes around the names of the tables and fields - no spaces then no need for those... surely?
Wouldnt the payment have a list of items paid for in a table somewhere? or is that what you are trying to find?
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Mar 28th, 2018, 12:57 AM
#4
Thread Starter
New Member
Re: Need Help in MYSQL Query
Thanks for the replies, I changed the query to below and it worked. Problem was to set left join dailysale with customer_payments
Code:
select customer.ID, customer.shop_name,
customer_payments.ID as customer_payments_ID,
customer_payments.customer_id,
customer_payments.billno,
customer_payments.cDate as customer_payments_cDate,
customer_payments.bill_amount,
customer_payments.paid_amount,
customer_payments.discount,
customer_payments.ret_amount,
customer_payments.balance,
customer_payments.description,
dailysale.ID as dailysale_ID, dailysale.payment_id,
dailysale.product_id, dailysale.qty,
dailysale.price, dailysale.total,
dailysale.description as dailysale_description,
product.ID as product_ID, product.product_name
from customer
left
join customer_payments
on (customer_payments.customer_id = customer.ID)
left join dailysale
on (customer_payments.ID = dailysale.payment_id)
left join product
on (dailysale.product_id = product.ID)
-
Mar 28th, 2018, 05:58 AM
#5
Re: [RESOLVED] Need Help in MYSQL Query
It's probably just my OCD and you're free to ignore this but I hate having to chain Left Joins together like that. To me a Left Join means "there might not be any data on the right of this join" but the join between dailysale and product is there because there may not be any data on the left - that's the exact oppposite of what a left join is meant to mean.
Something alot of people don't realise is that you can rearrange your joins and ons to give a more representative query:-
Code:
select customer.ID, customer.shop_name,
customer_payments.ID as customer_payments_ID,
customer_payments.customer_id,
customer_payments.billno,
customer_payments.cDate as customer_payments_cDate,
customer_payments.bill_amount,
customer_payments.paid_amount,
customer_payments.discount,
customer_payments.ret_amount,
customer_payments.balance,
customer_payments.description,
dailysale.ID as dailysale_ID, dailysale.payment_id,
dailysale.product_id, dailysale.qty,
dailysale.price, dailysale.total,
dailysale.description as dailysale_description,
product.ID as product_ID, product.product_name
from customer
left join customer_payments
on customer_payments.customer_id = customer.ID
left join (dailysale
join product
on dailysale.product_id = product.ID)
on customer_payments.ID = dailysale.payment_id
Now you have an inner join between daily sale and product which makes more sense because a sale will always have a product (presumably)
It shouldn't make any difference to the output or to performance but it does convey the meaning of the query a little better (or at least, I think so) which helps with maintainability.
NB. the brackets aren't strictly necessary. Again, they're just there to aid readability.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Mar 29th, 2018, 12:37 AM
#6
Thread Starter
New Member
Re: [RESOLVED] Need Help in MYSQL Query
Thanks dear but may be I didnt convey my problem correctly due to my english, but the seller told me that He some times just get paid and there is no product bcoz some times the buyer buy the products in several bills at different times and then Pay at once without buying new products.
Hope this time you will get the point.
-
Mar 29th, 2018, 08:46 AM
#7
Re: [RESOLVED] Need Help in MYSQL Query
Could you post a copy of your schema?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|