Databases
StackOverflow2013
Postsdbo
POMySQL join same table display rows in columns way (complicated)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL join same table display rows in columns way (complicated)
    primarykey
    data
    text
    <p>I would like to join the same table 4 times to get columns way to display, I am not sure is it possible do in 1 single SQL statements.</p> <p>tbl_username </p> <pre><code>id username 1 Adam 2 Bob 3 Chris </code></pre> <p>tbl_opportunity </p> <p><img src="https://i.stack.imgur.com/0Xqcx.png" alt="tbl_opportunity"></p> <p>I got these two table, I would like the outcome like this</p> <p><img src="https://i.stack.imgur.com/3qNtk.png" alt="enter image description here"></p> <p>I tested with following sql, but the outcome is wrong</p> <pre><code>SELECT users.`username`, COUNT(`proposal`.name) AS `Pro Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`proposal`.`amount`), 2)) AS pro_rm_amount, COUNT(`nego`.name) AS `nego Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`nego`.`amount`), 2)) AS nego_rm_amount, COUNT(`cw`.name) AS `cw Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cw`.`amount`), 2)) AS cw_rm_amount, COUNT(`cl`.name) AS `cl Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cl`.`amount`), 2)) AS cl_rm_amount FROM tbl_username users JOIN `tbl_opportunity` AS proposal ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Proposal' AND proposal.`deleted`=0 AND MONTH(`proposal`.date)= '1' AND YEAR(`proposal`.date)= '2012') LEFT JOIN `tbl_opportunity` AS nego ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Nego' AND nego.`deleted`=0 AND MONTH(`nego`.date)= '1' AND YEAR(`nego`.date)= '2012') LEFT JOIN `tbl_opportunity` AS cw ON (cw.`user_id` = users.id AND cw.`stage` = 'Win' AND cw.`deleted`=0 AND MONTH(`cw`.date)= '1' AND YEAR(`cw`.date)= '2012') LEFT JOIN `tbl_opportunity` AS cl ON (cl.`user_id` = users.id AND cl.`stage` = 'Lose' AND cl.`deleted`=0 AND MONTH(`cl`.date)= '1' AND YEAR(`cl`.date)= '2012') GROUP BY users.`username` </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShiro
    1. USShiro
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. USShiro
      1. VTFavorite
    3. VO
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthe sql statement and table a bit different due to I lazy to retype it again :p
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. USShiro
    2. COso which one are you trying to get an answer for? The example tables or the tables and columns referenced in the query that you have tried? If you need a definitive answer I suggest you take the time to make your example table layout and data and your example query match.
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. USTom Mac
    3. COHi @Tom Mac, I had updated the sql statement to match the case. Thanks!
      singulars
      1. POMySQL join same table display rows in columns way (complicated)
      1. USShiro
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload