Results 1 to 7 of 7

Thread: [RESOLVED] Need Help in MYSQL Query

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    12

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

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Need Help in MYSQL Query

    Moved to database development.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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?

    BOFH Now, BOFH Past, Information on duplicates

    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...

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    12

    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)

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    12

    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.

  7. #7
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: [RESOLVED] Need Help in MYSQL Query

    Could you post a copy of your schema?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

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
  •  



Click Here to Expand Forum to Full Width