[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)

---

Moved to database development.

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?

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)

---

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.

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.

Could you post a copy of your schema?