0

Having viewed several matrials both here on Stack Overflow and other external sources, I am attempting to write a piece of code to generate a view that performs a calculation utilising data from other tables, The aim of the code is:

  1. To Acquire username from the UserDetails table
  2. Acquire weight in pounds from the UserDetails Table
  3. Acquire distance_in_miles from the Cardiovascular Records table
  4. Use the weight and distance in a calculation as shown below which is output as the caloriesBurned column.

My attempt can be seen below:

CREATE VIEW CardioCaloriesBurned (username, weight, distance, caloriesBurned) AS
SELECT UserDetails.username, UserDetails.weight_in_pounds,
CardiovascularRecords.distance_in_miles , 
((0.75 X weight_in_pounds) X distance_in_miles)
FROM UserDetails, CardiovascularRecords

If anyone could help in correcting this issue It would be greatly appreciated.

Edit: I am getting a syntax error in SQLite Manager relating to a "(" however Im not seeing any issues myself.

Edit: Code for the Cardiovascular Table and UserDetails table below:

CREATE TABLE "CardiovascularRecords" ("record_ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE CHECK (record_ID>0) , "distance_in_miles" REAL NOT NULL CHECK (distance_in_miles>0) , "username" TEXT NOT NULL , "date" DATETIME DEFAULT CURRENT_DATE, "notes" TEXT(50), "start_time" TEXT, "end_time" TEXT, FOREIGN KEY(distance_in_miles) REFERENCES DistanceinMiles(distance_in_miles) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY(username) REFERENCES UserDetails(username) ON DELETE CASCADE ON UPDATE CASCADE)


CREATE TABLE "UserDetails" ("username" TEXT PRIMARY KEY  NOT NULL  UNIQUE  CHECK (length(username)>0), "password" TEXT NOT NULL  CHECK (length(password)>3), "email_address" TEXT NOT NULL  UNIQUE CHECK (length(email_address)>3) , "weight_in_pounds" REAL NOT NULL CHECK(weight_in_pounds>0) CHECK (length(weight_in_pounds)>0), "height_in_inches" REAL NOT NULL CHECK(height_in_inches>0) CHECK (length(height_in_inches)>0), "age" INTEGER CHECK(age>0), WITHOUT ROWID)

Thanks

JHB92

user3075454
  • 49
  • 6
  • you should `JOIN` both the tables. Show your table schema? – Rahul Apr 29 '14 at 19:12
  • I was informed at http://stackoverflow.com/questions/23297967/a-derived-field-in-an-sqlite3-database/23298042?noredirect=1#23298042 the above would work? and if by schema you mean the code or the image? not enough rep for diagrams sadly – user3075454 Apr 29 '14 at 19:14
  • BTW, your original question was about creating views, not JOINing tables. – Larry Lustig Apr 29 '14 at 19:17
  • No need of diagram .. you can paste the schema text in your post. – Rahul Apr 29 '14 at 19:19

1 Answers1

0

Your immediate error message is probably caused by the fact that you're using the letter X to indicate multiplication. It should be * (the capital 8 character on US keyboards).

After you've solved that problem, you may find that you have many, many more results in the output than you expect. That's because you're not telling the database which record in CardiovascularRecords to "hook up" for the purpose of doing the calculation. That may or may not (probably not) produce the results you want.

If you're not getting what you want, you must tell us which column or columns in UserDetails "point to" the record in CardiovascularRecords that applies to any given calculation.

CREATE VIEW CardioCaloriesBurned AS
SELECT UserDetails.username AS username, 
       UserDetails.weight_in_pounds AS weight,
       CardiovascularRecords.distance_in_miles AS distance, 
       ((0.75 * weight_in_pounds) * distance_in_miles) AS caloriesBurned
FROM UserDetails INNER JOIN CardiovascularRecords
ON UserDetails.username = CardiovascularRecords.username
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160