CODEWITHSUNDEEP
mysqlsql
pbrito
pbrito

Reputation: 84

MySql View with 2 instances of the same table

I have a question: I'm making a mysql view that is supposed to fetch data from 3 tables, except it's not 3 tables is actually 2 but i need the same table twice. I have the view all cooked up and its something like this:

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`))

There is only one problem, when I run a query on this view it returns a repeat of every single record, something like:

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*

Basically, it does a cartesian product on the last column, for each record on the "mother table", it makes a record for every line on the child table

Upvotes: 0

Views: 224

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270003

I think this is what you want:

CREATE VIEW ointeriorprecisadisto.com.new_view AS
    SELECT a.ad_title, a.ad_details,
           c.category_name AS ad_category_id,
           p.category_name AS ad_category_parent_id
    FROM ointeriorprecisadisto.com.wp_awpcp_ads a LEFT JOIN
         ointeriorprecisadisto.com.wp_awpcp_categories c
         ON a.ad_category_id = c.ad_category_id LEFT JOIN
         ointeriorprecisadisto.com.wp_awpcp_categories p
         ON a.ad_category_parent_id = p.category_id
--------------------------------------^

I think the problem is that you have the parent defined in two places. This seems like a bad design -- either the parent should be in the category table or the ads table. I'm not sure which you intend; this uses the parent from the ads.

Some notes:

  • You do not need to use backticks everywhere, particularly for table aliases.
  • I switched to a LEFT JOIN, in case some categories are missing.
  • You don't have to rename a column to the same name. So, a.ad_title as ad_title is redundant.

Upvotes: 1

minatverma
minatverma

Reputation: 1099

Try this:

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

Upvotes: 1

Related Questions