具有同一个表的 2 个实例的 MySql View

Question

我有一个问题:我正在制作一个 mysql View ，该 View 应该从 3 个表中获取数据，但它不是 3 个表，实际上是 2 个表，但我需要同一个表两次。我把所有的 View 都准备好了，它是这样的:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ointeriorprecisadisto.com`.`new_view` AS
    SELECT 
        `a`.`ad_title` AS `ad_title`,
        `a`.`ad_details` AS `ad_details`,
        `b1`.`category_name` AS `ad_category_id`,
        `b2`.`category_name` AS `ad_category_parent_id`
    FROM
        ((`ointeriorprecisadisto.com`.`wp_awpcp_ads` `a`
        JOIN `ointeriorprecisadisto.com`.`wp_awpcp_categories` `b1`)
        JOIN `ointeriorprecisadisto.com`.`wp_awpcp_categories` `b2`)
    WHERE
        ((`a`.`ad_category_id` = `b1`.`category_id`)
            AND (`a`.`ad_category_parent_id` = `b2`.`category_parent_id`))

只有一个问题，当我在此 View 上运行查询时，它会返回每个记录的重复，例如:

title    | details  | cat_id   | parent_ id
-------------------------------------
record 1 | record 1 | record 1 | record 1
record 1 | record 1 | record 1 | *record that shouldnt be here*
record 1 | record 1 | record 1 | *another one*
record 1 | record 1 | record 1 | *and another one*

基本上，它在最后一列上执行笛卡尔积，对于“母表”上的每条记录，它会为子表上的每一行创建一条记录

Answer 1

试试这个:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ointeriorprecisadisto.com`.`new_view` AS
    SELECT 
        `a`.`ad_title` AS `ad_title`,
        `a`.`ad_details` AS `ad_details`,
        `b1`.`category_name` AS `ad_category_id`,
        `b1`.`category_name` AS `ad_category_parent_id`
    FROM    `ointeriorprecisadisto.com`.`wp_awpcp_ads` `a`
    INNER JOIN `ointeriorprecisadisto.com`.`wp_awpcp_categories` `b1`
    ON  `a`.`ad_category_id` = `b1`.`category_id`
    AND `a`.`ad_category_parent_id` = `b1`.`category_parent_id`;