-
Notifications
You must be signed in to change notification settings - Fork 0
/
view.sql
44 lines (23 loc) · 1.62 KB
/
view.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE OR REPLACE VIEW itemsView AS SELECT items.* ,categories.* FROM items INNER JOIN categories
ON categories.categories_id
=items.items_categories
CREATE OR REPLACE VIEW myFavorite AS
SELECT favorite.* ,items.*, users.users_id FROM favorite
INNER JOIN users ON users.users_id =favorite.favorite_userId
INNER JOIN items ON items_id =favorite.favorite_itemsId
-- CREATE OR REPLACE VIEW cartView AS
-- SELECT items.*,cart.*,SUM(items.items_price)As
-- itemsPriceAll,SUM(( items_price - ( items_price * items_discount / 100 ))) AS itemsPriceAllWithDiscount,COUNT(cart.cart_itemsId) AS itemsCountAll FROM items INNER JOIN
-- cart ON cart.cart_itemsId =items.items_id GROUP BY cart.cart_userId,cart.cart_itemsId,cart.cart_orderId
CREATE OR REPLACE VIEW cartView AS
SELECT items.*,cart.*,SUM(items.items_price)As
itemsPriceAll,SUM(( items_price - ( items_price * items_discount / 100 ))) AS itemsPriceAllWithDiscount,COUNT(cart.cart_itemsId) AS itemsCountAll FROM items INNER JOIN
cart ON cart.cart_itemsId =items.items_id WHERE cart_orderId=0 GROUP BY cart.cart_userId,cart.cart_itemsId ,cart.cart_orderId
CREATE OR REPLACE VIEW orderView AS SELECT orders.*,address.* FROM orders
LEFT JOIN address ON address.address_id=orders.orders_addressId
CREATE OR REPLACE VIEW orderDetailsView AS
SELECT items.*,cart.*,SUM(items.items_price)As
itemsPriceAll,SUM(( items_price - ( items_price * items_discount / 100 ))) AS itemsPriceAllWithDiscount,COUNT(cart.cart_itemsId) AS itemsCountAll FROM items INNER JOIN
cart ON cart.cart_itemsId =items.items_id
WHERE cart_orderId!=0
GROUP BY cart.cart_userId,cart.cart_itemsId ,cart.cart_orderId