Member Avatar for samuel_1991

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
  • 13K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by samuel_1991

Dani AI

Generated

When you join multiple tables, SELECT * returns every column from each source, so shared keys (like ShopCartID) appear once per table. In T-SQL there is no SELECT * EXCEPT (...); the fix is to project exactly the columns you want and alias any duplicate names. Keep the authoritative copy of the key from one table and omit the others. Also prefer short table aliases to keep the select list readable.

Example that selects one ShopCartID, disambiguates same-named columns, and keeps only useful fields:

SELECT
  sci.ProductID,
  sci.Name,
  sci.Price,
  sci.Quantity AS ItemQty,
  sc.ShopCartID,          -- keep a single copy
  sc.ShopperID,
  sc.DateCreated,
  od.OrderID,
  od.ShipName,
  od.DateOrdered
FROM dbo.ShopCartItem AS sci
JOIN dbo.ShopCart     AS sc ON sc.ShopCartID = sci.ShopCartID
JOIN dbo.OrderData    AS od ON od.ShopCartID = sc.ShopCartID;

If you need this projection often, wrap it in a view so the column list is maintained in one place:

CREATE VIEW dbo.vOrderLines AS
SELECT ...same select list as above...

Then query SELECT * FROM dbo.vOrderLines; without exposing redundant columns. See SELECT - Transact-SQL for projection and aliasing, and CREATE VIEW for encapsulating common joins.

Recommended Answers

All 2 Replies

Member Avatar for sknake

>>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 .

Member Avatar for samuel_1991

Cool, that works! Addon: Maybe must use

TableName.Col1

etc.

Edited by samuel_1991 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.