CODEWITHSUNDEEP
mysqlsql
SwampYeti
SwampYeti

Reputation: 387

MySQL Create View from 2 different tables and columns

I am trying to create a view of 2 tables.

Currently, I am using the line below and this works fine, but I am getting too much information back, I need to be more selective and choose columns:

first table

wp_cart66_orders and i need to pull 
bill_first_name_, bill_last_name

status=, new or shipped etc...

2nd table

wp_cart_66_order_items
description, quantity

I am not sure if I need to create a view or just use this query.

Also, I might need to be pointed in the right direction on how to create it if that is the case.

select wp_cart66_orders.*, wp_cart66_order_items.*
from wp_cart66_orders, wp_cart66_order_items
where wp_cart66_orders.id=wp_cart66_order_items.order_id
    and wp_cart66_orders.status = 'new';

Thanks.

Upvotes: 0

Views: 4605

Answers (3)

ljh
ljh

Reputation: 2594

Write your selective columns, your join explicitly, and reanme the common column names from two tables. 


create view OrderItemsVW
as 
select wp_cart66_orders.bill_first_name as Bill_First_Name,   
       wp_cart66_orders.bill_last_name as Bill_Last_Name,  
       wp_cart66_orders.Description as OrdersDescription,          
       wp_cart66_order_items.Description as OrderItemsDescription
from wp_cart66_orders 
inner join wp_cart66_order_items
    on wp_cart66_orders.id=wp_cart66_order_items.order_id
where wp_cart66_orders.status = 'new';

Upvotes: 2

Lucian Depold
Lucian Depold

Reputation: 2019

Use the as operator or list every column you want:

Select table1.col1 AS mycolname, table2.col4 AS mycolname2...

or

Select table1.col1,table2.col4 ...

Upvotes: 0

gorootde
gorootde

Reputation: 4073

A view will somehow be faster and can be reused. However, you may also just use your select query.

select wp_cart66_orders.*, wp_cart66_order_items.* from wp_cart66_orders, wp_cart66_order_items where wp_cart66_orders.id=wp_cart66_order_items.order_id and wp_cart66_orders.status = 'new';

this will only work if wp_cart66_orders and wp_cart66_order_items have the same columns.

Upvotes: 0

Related Questions