CODEWITHSUNDEEP
sql
user2950593
user2950593

Reputation: 9627

select from two sql views not using one of them

I have this sql view, this is not my code:

SELECT
    `view_combined_rev_data`.`date` AS `date`,
    `view_combined_rev_data`.`book_title` AS `book_title`,
    `view_combined_rev_data`.`marketplace` AS `marketplace`,
    `view_combined_rev_data`.`amazon_kdp_avg_list_price` AS `amazon_kdp_avg_list_price`,
    `view_combined_rev_data`.`amazon_kdp_royalty_type` AS `amazon_kdp_royalty_type`,
    `view_combined_rev_data`.`amazon_kdp_revenue_in_usd` AS `amazon_kdp_revenue_in_usd`,
    `view_combined_rev_data`.`amazon_kdp_royalty_in_usd` AS `amazon_kdp_royalty_in_usd`,
    `view_combined_rev_data`.`amazon_kdp_paid_downloads` AS `amazon_kdp_paid_downloads`,
    `view_combined_rev_data`.`amazon_kdp_free_downloads` AS `amazon_kdp_free_downloads`,
    `view_combined_rev_data`.`amazon_ku_pages_read` AS `amazon_ku_pages_read`,
    `view_combined_rev_data`.`amazon_ku_revenue_in_usd` AS `amazon_ku_revenue_in_usd`,
    `view_combined_rev_data`.`create_space_revenue_in_usd` AS `create_space_revenue_in_usd`,
    `view_combined_rev_data`.`create_space_royalty_in_usd` AS `create_space_royalty_in_usd`,
    `view_combined_rev_data`.`create_space_paperbacks_sold` AS `create_space_paperbacks_sold`,
    (
        (
            `view_combined_rev_data`.`amazon_kdp_revenue_in_usd` + `view_combined_rev_data`.`amazon_ku_revenue_in_usd`
        ) + `view_combined_rev_data`.`create_space_revenue_in_usd`
    ) AS `daily_total_revenue`,
    (
        (
            `view_combined_rev_data`.`amazon_kdp_royalty_in_usd` + `view_combined_rev_data`.`create_space_royalty_in_usd`
        ) + `view_combined_rev_data`.`amazon_ku_revenue_in_usd`
    ) AS `daily_total_royalty`
FROM
    `view_combined_rev_marketplace_data` `view_combined_rev_data`

My question is simple: Why view_combined_rev_marketplace_data is used in this line. I can't find the code using it anywhere else, so can I simply remove it?

FROM
    `view_combined_rev_marketplace_data` `view_combined_rev_data`

Upvotes: 1

Views: 28

Answers (2)

Vladislav
Vladislav

Reputation: 2982

FROM
    `view_combined_rev_marketplace_data` `view_combined_rev_data`

These are not two views, but one view view_combined_rev_marketplace_data with an alias view_combined_rev_data.

When an alias is used to reference a table/view/function, then it must be used in the statement instead of the object's name. Usually aliases are meant to provide a shorter or more readable reference to the SQL object. In this case it is relatively long.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is your FROM clause:

FROM `view_combined_rev_marketplace_data` `view_combined_rev_data`

The first name, view_combined_rev_marketplace_data is the name of a table or view (presumably a view) that exists in the database.

The second name, view_combined_rev_data, is a table alias. This is how the table/view is referred to in the query.

I recommend that you use table/view aliases that are abbreviations for the table/view name, something like this:

FROM `view_combined_rev_marketplace_data` vcrmd

Then the references to columns would look like:

SELECT vcrmd.`date` AS `date`,
       vcrmd.`book_title` AS `book_title`,
       . . .

And this would further simplify to:

SELECT vcrmd.`date`,
       vcrmd.`book_title`,
       . . .

The column alias (name given after the as) is unnecessary in this case, because it defaults to the column name. Note, though, that local coding styles may recommend having explicit column aliases.

Upvotes: 1

Related Questions