for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have

Events with payouts for the partners of company

i have 3 tables (t_company, t_partner, t_event, t_payout).

t_company = the firms t_partner = partner of companies t_event = Events of Companies and Partners t_payout = prices and payouts for events

t_company: i_company INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, s_caption VARCHAR(254) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (i_company)

  t_partner:
`i_link` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `i_licensee` INT(10) UNSIGNED NOT NULL,
    `i_partner` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`i_link`),

  t_event:
    `i_event` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `i_dealer_office` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`i_event`)

  t_payout:
    `i_payout` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `s_caption` VARCHAR(254) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `i_category` CHAR(4) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `n_amount` DECIMAL(9,2) NULL DEFAULT NULL,
    `d_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `i_event` INT(10) UNSIGNED NULL DEFAULT NULL,
    `d_date` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`i_payout`),

What i need?

  1. PartnerIDs of i.e. Company with ID3 (i_partner -> t_partner)
  2. Company Name (s_caption -> t_company)
  3. Number of Events with Payouts from the partners of Company

QUERY

SELECT 
    p.i_partner,
    (select c1.s_caption FROM t_company c1 WHERE c1.i_company = p.i_partner) as s_caption, 
    (SELECT count(*) from t_event e Where e.i_dealer_office = p.i_partner ) as testing
   ,    (
            Select count(*) from (
                SELECT count(payout.i_event) from  t_event e 
                 LEFT JOIN t_payout payout ON payout.i_event = e.i_event
                 WHERE
 e.i_dealer =  p.i_partner 
### here i tried p.i_partner for the partnerIDs but thats doesnt work #### AND payout.d_time > '2018-01-01 00:00:00'
                Group by e.i_event) as result

    )as NumberOfPayouts

    FROM t_company c
    LEFT JOIN t_link_partner p ON p.i_licensee = c.i_company
    WHERE c.i_company = 3  <= the company
    ORDER BY s_caption asc
  • i have 3 tables (t_company, t_partner, t_event, t_payout). I count with a result of 4. And add links info. What i need? I cannot find the reason for to use subqueries. – Akina Aug 10 at 7:24
  • ok , thanks for feedback, but i have no solution. – B.Irneos Aug 12 at 11:00
  • @Akina I ve now found my own solution...thanks anyway Here my solution... select t.i_partner, (select t.s_caption from t_company t where t.i_company = t.i_partner ), (select count(*) from t_event Where t_event.i_dealer = t.i_partner ) as NumberOfPayouts FROM t_link_partner t, t_event, t_payout, t_company WHERE t_event.i_dealer = t.i_partner AND t_payout.i_event = t_event.i_event AND t_company.i_company=3 group by t.i_partner – B.Irneos Aug 12 at 11:57

Thanks for your support, now i found my own solution:

    select
       t.i_partner, 
      (select t.s_caption from t_company t where t.i_company = t.i_partner ),
(select count(*) from t_event Where t_event.i_dealer = t.i_partner ) as NumberOfPayouts

FROM 
    t_link_partner t, t_event, t_payout, t_company
WHERE
      t_event.i_dealer = t.i_partner AND t_payout.i_event = t_event.i_event AND t_company.i_company=3
group by t.i_partner

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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