连接查询以将多行中的数据转换为单行

Question

Selecting data from multiple rows into a single row

Quote_ws table

quote_ws_id     quote_id    gross       tax
101                 79      98.25       13.0
102                 79      91.25       12.5    
103                 79      94.25       11.0
104                 79      92.25       11.5
105                 79      96.25       12.0


Section_table

section_id     quote_id    lsb(quote_ws_id)   non_lsb(quote_ws_id)
1               79          101                     null
2               79          102                     103
3               79          104                     105

I am trying to create a MYSQL query that will return gross and tax values from table "Quote_ws" based on lsb and non lsb values in 'Section' table.The results would be:

section_id     quote_id    gross                tax
1               79          98.25               13.0


2               79          91.25 + 94.25       12.5+11.0
                        (lsb 102,non-lsb 103)   (lsb 102,non-lsb 103)

3               79          92.25 + 96.25       11.5+12.0       
                        (lsb 104,non-lsb 105)   (lsb 104,non-lsb 105)

I am new to SQL. How can I do that? Please help me ??

Answer 1

SELECT
s.section_id,
s.quote_id,
(IFNULL(q_lbs.gross, 0)+IFNULL(q_non_lbs.gross, 0)) as gross,
(IFNULL(q_lbs.tax, 0)+IFNULL(q_non_lbs.tax, 0)) as tax,
FROM
Section_table s

LEFT JOIN Quote_ws q_lbs
ON q.quote_id = q.quote_id

LEFT JOIN Quote_ws q_non_lbs
ON q.quote_id = q.quote_id;