John Siniger John Siniger - 4 months ago 13
SQL Question

Limit Row Display on Left Join with Where Clause

I have a query which first is selecting one row from table cases and after that I have selection of 5 rows from join table payments. The results of the select is showing 5 results. I would like to limit it to show only one result the first select but still ti have selected all the 5 results from the join table in the memory.

My query so far is the following:

SELECT c.*,
DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),'%Y-%m-%d') AS expiration_date_30,
(SELECT name FROM members rp WHERE rp.id = c.resonsible_partner) as responsible_partner,
FROM cases c
LEFT JOIN payments AS inv ON inv.caseid = c.id
WHERE c.id = $id


Any Help will be welcome!

Sample output in this case should display:

from table cases should display
c.*
on screen:
id, casename, proposal_date
this is what is should controls how many rows will have to be displayed.

id | casename | proposal_date |
------------------------------
300 | One | 2016-05-05 |


from table payments should display:
payment_percentage_01 = 25, payment_percentage_02 = 30, payment_number_01 = 1, payment_number_02 = 2


id | caseid | payment_percentage | payment number |
---------------------------------------------------
1 | 300 | 25 | 1 |
2 | 300 | 30 | 2 |

Answer

This seems to be a table pivot issue, not sure about if your really want this, but just try it, maybe work:

SELECT
    c.*,
    DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),'%Y-%m-%d') AS expiration_date_30,
    -- (SELECT name FROM members rp WHERE rp.id = c.resonsible_partner) as responsible_partner,
    MAX(CASE WHEN p.id = 1 THEN p.payment_percentage END) AS payment_percentage_01,
    MAX(CASE WHEN p.id = 2 THEN p.payment_percentage END) AS payment_percentage_02,
    MAX(CASE WHEN p.id = 1 THEN p.`payment number` END) AS payment_number_01,
    MAX(CASE WHEN p.id = 2 THEN p.`payment number` END) AS payment_number_02
FROM cases c
LEFT JOIN payments p
ON c.id = p.caseid
GROUP BY c.id

SQLFiddle Demo

Edited:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN p.id = ''',
      id,
      ''' THEN p.payment_percentage END) AS payment_percentage',
      id
    ), ',',
     CONCAT(
      'MAX(CASE WHEN p.id = ''',
      id,
      ''' THEN p.`payment number` END) AS payment_number',
      id
    )
  ) INTO @sql
FROM payments;
SET @sql = CONCAT('
    SELECT c.*,
    DATE_FORMAT( ADDDATE(c.proposal_date, INTERVAL 30 DAY),''%Y-%m-%d'') AS expiration_date_30, '
   , @sql
   , ' FROM cases c
       LEFT JOIN payments p
       ON c.id = p.caseid
       GROUP BY c.id;');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo