Missing column in MySQL query when joining tables

Question

I have the following tables:

    CREATE TABLE `bonuses` (
    `id` int(11) NOT NULL,
    `ugID` int(11) NOT NULL,
    `bonusMin` int(11) NOT NULL,
    `bonusMax` int(11) NOT NULL,
    `bonus` decimal(10,3) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    ALTER TABLE `bonuses`
      ADD UNIQUE KEY `id` (`id`);

    CREATE TABLE `salaries` (
    `id` int(11) NOT NULL,
    `ugID` int(11) NOT NULL,
    `basePay` decimal(10,3) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    ALTER TABLE `salaries`
    ADD UNIQUE KEY `id_2` (`id`),
    ADD KEY `id` (`id`);

    CREATE TABLE `Summary` (
    `id` int(11) NOT NULL,
    `datum` date NOT NULL,
    `startAt` time NOT NULL,
    `endAt` time NOT NULL,
    `ugID` int(11) NOT NULL,
    `xMessages` int(12) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    ALTER TABLE `Summary`
    ADD KEY `id` (`id`);
    ALTER TABLE `Summary`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

    CREATE TABLE `ug` (
    `id` int(11) NOT NULL,
    `ugName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `ugBonus` tinyint(1) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    ALTER TABLE `ug`
    ADD PRIMARY KEY (`id`),
    ADD KEY `id` (`id`);
    ALTER TABLE `ug`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

I am trying to join these tables in a query to get the information I want.

The query looks like this:

    SELECT su.datum as datum, su.startAt as startAt, su.endAt as endAt, su.xMessages as xMessages,
    ug.id as ugID, ug.ugName as Platform, ug.ugBonus as HasBonus, sal.basePay as basePay,
    bo.bonusMin as minBonus, bo.bonusMax as maxBonus, bo.bonus as sumBonus
    FROM Summary su
    INNER JOIN ug as ug
    ON su.ugID = ug.id
    LEFT JOIN salaries AS sal
    ON ug.id = sal.ugID
    LEFT JOIN bonuses as bo
    ON bo.ugID= su.ugID
    WHERE (su.xMessages > bo.bonusMin AND su.xMessages < bo.bonusMax)
    ORDER BY datum DESC

When I run this query, I am getting the results I want, except for the column "basePay".

I have tried different types of join and in different orders. I guess the mainproblem is primary/forign keys on the tables as well as not joining the right way.

Hope someone can point out what I should do, both regarding to tables and how to make the query correct. Have tried to find the answer since yesterday, both here and by googling, but to no avail.

Answer 1

等待大神答复