#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0

    Query works but creating VIEW it crash


    Testing the following query, it works fine:

    Code:
    SELECT
        	mg_term_relationships.object_id, 
        	mg_term_relationships.term_taxonomy_id ,  
        	mg_term_taxonomy.term_id ,  
        	mg_term_taxonomy.term_taxonomy_id , 
        	mg_termS.term_id ,  
        	mg_terms.name
            
        FROM mg_term_relationships
        
        JOIN mg_term_taxonomy ON mg_term_taxonomy.term_taxonomy_id = mg_term_relationships.term_taxonomy_id
        JOIN mg_terms ON mg_terms.term_id= mg_term_taxonomy.term_id
        
        WHERE mg_term_taxonomy.taxonomy="product_brand"
    When I create the related VIEW adding

    Code:
        CREATE VIEW brand2product AS
        SELECT
            mg_term_relationships.object_id, 
            mg_term_relationships.term_taxonomy_id ,  
            mg_term_taxonomy.term_id ,  
            mg_term_taxonomy.term_taxonomy_id , 
            mg_termS.term_id ,  
            mg_terms.name
        
        FROM mg_term_relationships
        
        JOIN mg_term_taxonomy ON mg_term_taxonomy.term_taxonomy_id = mg_term_relationships.term_taxonomy_id
        JOIN mg_terms ON mg_terms.term_id= mg_term_taxonomy.term_id
        
        WHERE mg_term_taxonomy.taxonomy="product_brand"
    it reports to following error:

    Code:
    #1060 Duplicate column name "term_taxonomy_id"
    even if the field is identified by the table name "term_taxonomy".

    Any help?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    It was solved renaming with AS each field into SELECT.
  4. #3
  5. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    If you're curious why, the reason is that views are like tables. Like tables, they cannot have multiple columns with the same name. The column names and types in your view are derived from the name and type of the result set that comprises the view. Your result set contains two separate term_taxonomy_id and term_id columns in the result. The fact that they originate from different tables doesn't matter.

    You can resolve the problem with an alias as you mentioned, however since both columns have the same value there's really no need for both of them to be in the result set. You could just remove one of them from the select list and resolve the issue that way.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,698
    Rep Power
    4288
    Originally Posted by kicken
    however since both columns have the same value
    ding ding ding we have a winner!

    but only for inner joins...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    @rudydotca
    late but I arrived, thanks for the additional information
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Many thanks for your full and interesting explanation.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Kicken, since the I need to have an updated list of the products with all related information that comes from different queries, is it better to create a VIEW and keep it updated trough a query or it's better to report the complex query every time is needed.
    The update operation should be done through a php script.
  14. #8
  15. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    I'm not sure what you're asking. There's generally no difference in execution between a view and the raw query. View's basically just let you either create simple versions of long queries or let you create easily reusable sub-queries.

    For example one system I work on deals with students in a school. Data you'd expect on a transcript is spread across many tables and requires many joins to gather up. Many other queries in the system can use this transcript data (or a subset of it) as a convenient starting point so I created a view that will create a virtual transcript_data table, then my other queries based on that view. So a query to for example determine courses that need enrolled can be simple like:
    Code:
    select *
    from transcript_data td
    inner join course_catalog  cc on td.catalogId=cc.catalogId
    where
        td.studentId=xxx
       and td.enrollmentId is null
    rather than some giant monstrosity (the query to create transcript_data is around 200 lines long).

    In addition if I ever need to modify how the transcript data is queried I don't have to go through the system changing every other query that depends on it, just the view.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Kichen I am not sure I catched your explanation just because I am at very beginning level however I understood transcript_data makes life easier May be I will come up with a more detailed question about my project.

    Thanks
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Hi guys,
    I come with an issue I am experiencing with pivot table. I have this table "postmeta" :

    product_id | meta_key | meta_value
    -----------------------------------------------------------
    prod 1 | regular_price | 90
    prod 1 | sales_price | 50
    prod 2 | regular_price | 80
    prod 2 | sales_price | 35


    I would to transform into:

    product_id | regular_price | sale price
    -----------------------------------------------------------
    prod 1 | 90 | 50
    prod 2 | 80 | 35


    For that I am using the following sql query to generate data as needed:

    Code:
    SELECT  post_id,
        COUNT(
            CASE 
                WHEN mg_postmeta.meta_key='_regular_price' 
                THEN 1 
                ELSE NULL 
            END
        ) AS 'RegPrice',
        COUNT(
            CASE 
                WHEN mg_postmeta.meta_key='_sales_price' 
                THEN 1 
                ELSE NULL 
            END
        ) AS 'SalesPrice'
        FROM   mg_postmeta
    
    GROUP BY mg_postmeta.post_id
    but as result I got 1 for "regular price" column and 0 for "sales price", and not the values listed in the original table.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,698
    Rep Power
    4288
    Originally Posted by smaa
    but as result I got 1 for "regular price" column and 0 for "sales price", and not the values listed in the original table.
    that's because you used COUNT

    try MAX, like this --
    Code:
    SELECT post_id
         , MAX( CASE WHEN meta_key = '_regular_price' 
                     THEN meta_value 
                     ELSE NULL END ) AS RegPrice
         , MAX( CASE WHEN meta_key = '_sales_price' 
                     THEN meta_value 
                     ELSE NULL END ) AS SalesPrice
      FROM mg_postmeta
    GROUP 
        BY post_id

    Comments on this post

    • smaa agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Yes Rudy, I got it implementing the query in this way:

    Code:
    SELECT p.ID, p.post_title, 
       MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
       MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
    FROM mg_posts p LEFT JOIN mg_postmeta pm1 ON ( pm1.post_id = p.ID)                 
    WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
    GROUP BY p.ID, p.post_title
    and it generated the expected result.

    Thank you!!
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Location
    ITALY
    Posts
    13
    Rep Power
    0
    Yes Rudy, I got it implementing the query in this way:

    Code:
    SELECT p.ID, p.post_title, 
       MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
       MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
    FROM mg_posts p LEFT JOIN mg_postmeta pm1 ON ( pm1.post_id = p.ID)                 
    WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
    GROUP BY p.ID, p.post_title
    and it generated the expected result.

    Thank you!!

IMN logo majestic logo threadwatch logo seochat tools logo