CODEWITHSUNDEEP
sqlsql-server
user10021926
user10021926

Reputation:

SQL Server - Querying database to find the difference between two columns

I am trying to display the difference between water measurements for each cardinal direction (N, E, S, W) for different vegetables. I think I am on the right track but not sure.

Here are the create table statements

CREATE TABLE Location
(
     LocationID int IDENTITY(0, 1) NOT NULL
,    LocationExposure varchar(50) NOT NULL
,    LocationSunlightPercentage numeric(3,2) NOT NULL
,    LocationMoistureLevel numeric(3, 2) NOT NULL
,    CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)

CREATE TABLE Crop
(
     CropID int IDENTITY(0, 1) NOT NULL
,    CropName varchar(50)
,    CropOptimalSunlightPercentage numeric(3,2) NOT NULL
,    CropOptimalMoistureLevel numeric(3,2) NOT NULL
,    CropExpectedHarvestWeight numeric(3,2) NOT NULL
,    CONSTRAINT PK_Crop PRIMARY KEY (CropID)
)

CREATE TABLE Sowing
(
     CropID int NOT NULL
,    TechnicianID int NOT NULL
,    LocationID int NOT NULL
,    SowingDate date NOT NULL
,    SowingSeedCount int NOT NULL
,    CONSTRAINT PK_Sowing PRIMARY KEY (CropID, TechnicianID, LocationID)
,    CONSTRAINT FK_SowingCrop FOREIGN KEY (CropID) REFERENCES Crop(CropID) ON DELETE CASCADE
,    CONSTRAINT FK_SowingTechnician FOREIGN KEY (TechnicianID) REFERENCES Technician(TechnicianID) ON DELETE CASCADE
,    CONSTRAINT FK_SowingLocation FOREIGN KEY (LocationID) REFERENCES Location(LocationID) ON DELETE CASCADE
)

Here are the dummy data

INSERT INTO Location VALUES ('East', 0.28, 0.80)
INSERT INTO Location VALUES ('North', 0.17, 0.84)
INSERT INTO Location VALUES ('West', 0.38, 0.48)
INSERT INTO Location VALUES ('South', 0.45, 0.66)

INSERT INTO Crop VALUES ('Carrot', 0.26, 0.82, 0.08)
INSERT INTO Crop VALUES ('Beet', 0.44, 0.80, 0.04)
INSERT INTO Crop VALUES ('Corn', 0.44, 0.76, 0.26)
INSERT INTO Crop VALUES ('Tomato', 0.42, 0.80, 0.16)
INSERT INTO Crop VALUES ('Radish', 0.28, 0.84, 0.02)

INSERT INTO Sowing VALUES (0, 0, 0, '20050418', 28)
INSERT INTO Sowing VALUES (0, 1, 1, '20050414', 14)
INSERT INTO Sowing VALUES (1, 0, 2, '20050418', 36)
INSERT INTO Sowing VALUES (2, 1, 3, '20050414', 20)
INSERT INTO Sowing VALUES (2, 2, 2, '20050419', 12)
INSERT INTO Sowing VALUES (3, 3, 3, '20050425', 38)
INSERT INTO Sowing VALUES (4, 2, 0, '20050430', 30)

And query thus far

SELECT CropName AS [Crop], LocationExposure AS [Location], CropOptimalMoistureLevel AS [Water Needed],  
       LocationMoistureLevel AS [Water Available], 
       CropOptimalMoistureLevel - LocationMoistureLevel AS [Difference]
FROM Sowing S JOIN
     Location L
     ON L.LocationID = S.LocationID JOIN
     Crop C
     ON C.CropID = S.CropID

Here is an output I want to get to start off with

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You don't need the Sowing table. You just want a CROSS JOIN:

SELECT c.CropName AS [Crop], l.LocationExposure AS [Location],
       c.CropOptimalMoistureLevel AS [Water Needed],  
       l.LocationMoistureLevel AS [Water Available], 
       (c.CropOptimalMoistureLevel - l.LocationMoistureLevel) AS [Difference]
FROM Location L CROSS JOIN
     Crop C
ORDER BY C.CropID, l.LocationExposure;

Upvotes: 2

Related Questions