0

Hi, I have a 3 tables needed to be inner join before I got a full details of a transaction history (What item, shipment details, quantity, who bought it etc).

The involved tables are ShopCart,ShopCartItem and OrderData

ShopCart:

CREATE TABLE dbo.ShopCart
(
  ShopCartID int IDENTITY (1,1),
  ShopperID int NOT NULL,
  OrderPlaced int NOT NULL DEFAULT (0),
  Quantity int NULL,  
  SubTotal money NULL,
  Tax money NULL,
  ShipCharge money NULL,
  Discount money NULL DEFAULT (0),
  Total money NULL,
  DateCreated datetime NULL DEFAULT (getdate()),
  CONSTRAINT PK_ShopCart PRIMARY KEY NONCLUSTERED (ShopCartID),
  CONSTRAINT FK_ShopCart_Shopper FOREIGN KEY (ShopperID) REFERENCES dbo.Shopper(ShopperID)
)
GO

ShopCartItem:

CREATE TABLE dbo.ShopCartItem 
(
  ShopCartID int,
  ProductID int,
  Name varchar (255) NULL,
  Price money NOT NULL,
  Quantity int NOT NULL,
  CONSTRAINT PK_ShopCartItem PRIMARY KEY NONCLUSTERED 
  (ShopCartID, ProductID),
  CONSTRAINT FK_ShopCartItem_ShopCart FOREIGN KEY (ShopCartID) 
  REFERENCES dbo.ShopCart (ShopCartID),
  CONSTRAINT FK_ShopCartItem_Product FOREIGN KEY (ProductID)
  REFERENCES dbo.Product (ProductID)
)
GO

OrderData:

CREATE TABLE dbo.OrderData
(
  OrderID int IDENTITY (1,1),
  ShopCartID int NOT NULL,
  ShipName varchar (50) NOT NULL,
  ShipAddress varchar (150) NOT NULL,
  ShipCountry varchar (50) NOT NULL,
  ShipPhone varchar (20) NULL,
  ShipEmail varchar (50) NULL,
  BillName varchar (50) NOT NULL,
  BillAddress varchar (150) NOT NULL,
  BillCountry varchar (50) NOT NULL,
  BillPhone varchar (20) NULL,
  BillEmail varchar (50) NULL,
  DeliveryDate datetime NULL,
  DeliveryTime varchar(50) NULL,
  DeliveryMode varchar(50) NULL,
  OrderStatus int NOT NULL DEFAULT (1),
  DateOrdered datetime NULL DEFAULT (getdate()),
  CONSTRAINT PK_OrderData PRIMARY KEY NONCLUSTERED (OrderID),
  CONSTRAINT FK_OrderData_ShopCart FOREIGN KEY (ShopCartID)
  REFERENCES dbo.ShopCart (ShopCartId)
)
GO

PS: I know the below query generally work but I want to remove the redudant column (ShopCartID showing 3 times)

SELECT * FROM ShopCartItem
Inner JOIN ShopCart ON ShopCartItem.ShopCartID = ShopCart.ShopCartID
Inner Join OrderData On ShopCartItem.ShopCartID = OrderData.ShopCartID
2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by samuel_1991
0

>>PS: I know the below query generally work but I want to remove the redudant column (ShopCartID showing 3 times)

You already have the logic for joining the three tables. You need to remove the Select * and use Select col1, col2, col3, col4 .

0

Cool, that works! Addon: Maybe must use

TableName.Col1

etc.

Edited by samuel_1991: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.