Compare rows between 2 tables
- by arthur
I am new to SQL and I need to build a database for a grocery store(not real, just a course assignment)
i have two fields from two different tables - supplied price - the price that the store buys from the supplier and price that is given to the customers
How can I make a constraint that insures that supplied price is lower then the price that is given to the customers?
The relevant tables that I have are:
CREATE TABLE Supplied_Products(
[Supplier ID] Int NOT NULL Foreign Key References Suppliers,
[Product ID] Int NOT NULL Foreign Key References Products,
Price Float NOT NULL,
CHECK (Price0),
Constraint PK_Supplied_Products PRIMARY KEY([Supplier ID] ,[Product ID])
)
CREATE TABLE Products(
[Product-ID] Int NOT NULL PRIMARY KEY,
[Product Name] Varchar(20) NOT NULL,
Price Float NOT NULL,
[Category-Name] Varchar(20) NOT NULL Foreign Key References Categories,
[Weight] Float NOT NULL,
[Is Refrigirated] Varchar(1) DEFAULT 'N'
CHECK ([Is Refrigirated] in('Y','N')),/* Is Refrigirated can be only Y-yes or N-no*/
CHECK (Price 0)
)
