1

Here is my mySQL query which I used to get the details from three tables.

    SELECT `pg`.`id`, 
ref, business_name, `usr`.`full_name`, 
date, `pmt`.`amount`, `pg`.`status` 
    FROM kv_ad_orders AS `pg` 
    LEFT JOIN kv_users AS `usr` 
           ON `pg`.`sales_rep_id` = `usr`.`ID` 
    RIGHT JOIN kv_payments AS `pmt` 
            ON `pg`.`ref` = `pmt`. `transId` 

here it shows only the kv_payments table entries only. Not the whole results. I want to display all the selected columns.

If I make it as left join table, than its showing other results except the amount.

2

If you want all the rows that have Orders and Payments but maybe don't have user go for the Inner Join/Left join

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status` 
  FROM kv_ad_orders AS `pg` 
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId` 
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID` 

If all the data must match, go for this:

SELECT `pg`.`id`, ref, business_name, `usr`.`full_name`, date, `pmt`.`amount`, `pg`.`status` 
  FROM kv_ad_orders AS `pg` 
INNER JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId` 
INNER JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID` 

If you are talking about query projection (columns to be returned regardless of the matching data) go for select pg.*

SELECT `pg`.*, `usr`.`full_name`, `pmt`.`amount`
  FROM kv_ad_orders AS `pg` 
LEFT JOIN kv_users AS `usr` ON `pg`.`sales_rep_id` = `usr`.`ID` 
RIGHT JOIN kv_payments AS `pmt` ON `pg`.`ref` = `pmt`. `transId` 
3
  • Thanks for your help. Actually i want the orders complete and payment and users are just picking one columns – Kvvaradha Aug 8 '16 at 11:41
  • If you are talking about query projection (not about matching rows) and want to show all the columns of kv_ad_orders go for select pg.* – vercelli Aug 8 '16 at 11:48
  • 1
    Not that one. but i can get some idea from that., and i solved my issue. its necessary to use both at left join or right join. – Kvvaradha Aug 8 '16 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.