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 ??
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;