CODEWITHSUNDEEP
sql
Tom Derod
Tom Derod

Reputation: 39

SQL SELECT query not working

SELECT s1.id, 
       s3.food_name, 
       Count(*) AS TotalRefill 
FROM   (SELECT ( s1.food_value - s2.food_value ) AS difference 
        FROM   `serving_info` s1, 
               `serving_info` s2 
        WHERE  s1.id - s2.id = '1' 
               AND s1.food_name = 'Shrimp' 
               AND s2.food_name = 'Shrimp') AS diff, 
       `serving_info` s3 
WHERE  s3.id = diff.id 
       AND s3.food_value >= '990' 
       AND diff.difference >= '150'

Result: #1054 - Unknown column 's1.id' in 'field list'

--
-- Table structure for table `employees`
--
CREATE TABLE IF NOT EXISTS `employees` (
  `id_user` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `email` varchar(64) NOT NULL,
  `phone_number` varchar(16) NOT NULL,
  `username` varchar(16) NOT NULL,
  `password` varchar(32) NOT NULL,
  `confirmcode` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id_user`),
  KEY (name)
);
--
-- Table structure for table `Foods`
--
CREATE TABLE IF NOT EXISTS `Foods` (
  `Food_name` varchar(40) NOT NULL,
  `CostPerRefill` double NOT NULL,
  PRIMARY KEY (`Food_name`)
);


--
-- Table structure for table `Serving_Info`
--
CREATE TABLE IF NOT EXISTS `Serving_Info` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `Food_Value` varchar(40) NOT NULL,
  `Food_name` varchar(40) NOT NULL,
  `Served_On` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `Oncall` varchar(128),
  Foreign key(`Oncall`) REFERENCES `employees`(`name`),
  Foreign key(`Food_name`) REFERENCES `Foods`(`Food_name`),
  PRIMARY KEY (`id`),
  UNIQUE (`Oncall`,`Food_name`, `Served_On`)
);

What is causing the s1 to not be declared? For some reason the s1 instance isn't detected by the s1.id. I have been trying to figure out from a while by changing different brackets but I really cannot figure out how to debug this..I have tried changing the position of the close bracket but that would mess up the query.

Upvotes: 0

Views: 78

Answers (4)

Surendra
Surendra

Reputation: 721

What all others here said is right, but just to give you a more preceise solution, look at the query below it should not be s1.id but it should be diff.id 

SELECT diff.id, 
       s3.food_name, 
       Count(*) AS TotalRefill 
FROM   (SELECT ( s1.food_value - s2.food_value ) AS difference, s1.id 
        FROM   `serving_info` s1, 
               `serving_info` s2
        WHERE  s1.id - s2.id = '1' 
               AND s1.food_name = 'Shrimp' 
               AND s2.food_name = 'Shrimp') AS diff, 
       `serving_info` s3 
WHERE  s3.id = diff.id 
       AND s3.food_value >= '990' 
       AND diff.difference >= '150'

Upvotes: 1

Gareth Williams
Gareth Williams

Reputation: 607

The table s1 is not in the from part of your query in which you are trying to select it, it's only in a subquery.

Upvotes: 0

Dijkgraaf
Dijkgraaf

Reputation: 11527

Because s1 is only defined in the inner select so not is scope. Select either diff.id or s3.id (they will be the same value as you are joining on it).

Upvotes: 0

Gary McGill
Gary McGill

Reputation: 27556

S1is defined inside the parentheses and thus not visible outside them, where you reference it.

Upvotes: 0

Related Questions