CODEWITHSUNDEEP
mysqlsql
UserNameM
UserNameM

Reputation: 85

Create views from tables with different number of columns mysql

I'm new to views in MySQL server.

I'm trying to create two VIEWS with "invoice_head" and "invoice_body", but in order to do that i need to get different keys from different tables (this tables have diferent number of columns).

This is the code that i'm trying to use:

CREATE VIEW invoice_head AS
SELECT
    date_order AS 'DATE OF ISSUE'
    FROM orders
UNION ALL
SELECT
    customer_name AS 'Client Name',
    customer_address AS 'Street adress', 
    customer_district AS 'District', 
    customer_country AS 'Portugal', 
    customer_zipcode AS 'ZIP Code'
    FROM customer;

but this code is giving me the error: The used SELECT statements have different number of columns.

And the tables i'm using are something like this (for example)

Customer Table (8 columns)

customer_id customer_name customer_address customer_district (...)
1 maria xxx street xxx lisbon (...)

Orders Table (9 columns)

order_id customer_id product_id date_order (...)
10 1 20 2020-12-15 (...)

The invoice_head is supposed to have information about the customer, the invoice_id, and the date of the order (invoice_date aka date_order).

Can someone tell me in what way is this possible to be made? Is it impossible to do or I need to do something I'm not seeing?

Upvotes: 0

Views: 47

Answers (1)

UserNameM
UserNameM

Reputation: 85

Someone in the comments helped me. I just needed to use the "Join" instead of "Select":

DROP VIEW IF EXISTS `invoice_head`;
CREATE VIEW `invoice_head` AS
SELECT  date_order as `Date of issue`,
        customer_name AS `Client Name`,
        customer_address AS `Street adress`, 
        customer_district AS `District`, 
        customer_country AS `Country`, 
        customer_zipcode AS `ZIP Code`
FROM orders
join customer
    on orders.customer_id = customer.customer_id;

Upvotes: 2

Related Questions