CODEWITHSUNDEEP
sql
Dani
Dani

Reputation: 11

How can I do a SELECT for a subquery that has returned more than one value?

I have a problem with using SQL in a database.

This is my code:

CREATE TABLE Supplier 
(
    Supplier_ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    Phone int,
    Email varchar(50) 
);

CREATE TABLE Batchofmeat 
(
    Number_of_batch varchar(50) NOT NULL PRIMARY KEY,
    Supplier_ID int NOT NULL REFERENCES Supplier(Supplier_ID),
    Weight FLOAT NOT NULL,
    Temperature FLOAT NOT NULL,
    Price FLOAT NOT NULL
); 

SELECT Email 
FROM Supplier 
WHERE (SELECT Temperature FROM Batchofmeat 
       WHERE Supplier.Supplier_ID = Batchofmeat.Supplier_ID) < 3;

The problem is that when I run the SELECT, I get the following error:

The subquery has returned more than one value, which is not correct when following =, !=, <, <=, >, >= or when used as an expression

Even if I get the error it should show the solution, what could be the problem?

What I want with my select is to select the email address of those suppliers whose Batchofmeat have a temperature below 3°C.

Upvotes: 0

Views: 54

Answers (4)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

If could use avg, max, or min temperature if that meets your business requirements.

SELECT Email 
  FROM Supplier 
 WHERE 
   (
      SELECT avg(Temperature)
        FROM Batchofmeat 
       WHERE Supplier.Supplier_ID = Batchofmeat.Supplier_ID
   ) < 3;

Having said that, I would write the SQL query like this:

SELECT Supplier_ID, Email, avg(Temperature)
  FROM Supplier inner join Batchofmeat using (Supplier_ID)
 group by Supplier_ID, Email
having avg(Temperature) < 3;

UPDATE:

Per your comment: What I want with my select is to select the email address of those suppliers whose Batchofmeat have a temperature below 3°C. –

Do this:

SELECT distinct Supplier_ID, Email
  FROM Supplier inner join Batchofmeat using (Supplier_ID)
 where Temperature < 3;

Upvotes: 0

Stu
Stu

Reputation: 32614

It sounds like exists is all you require here:

select email
from supplier s
where exists (select * from BatchOfMeat b where b.temperature < 3 and b.Supplier_Id=s.Supplier_Id )

Upvotes: 0

trillion
trillion

Reputation: 1401

Since you are adding conditions such as: temperature <3 in your query it is possible that there could be multiple values that meet this criteria. Hence you will have to use the in operator here.

in acts like a list of values.

SELECT Email FROM Supplier 
WHERE Supplier_ID IN (SELECT Temperature FROM Batchofmeat 
                      WHERE Supplier.Supplier_ID =  Batchofmeat.Supplier_ID AND Temperature < 3 );

Upvotes: 0

Rodney Enke
Rodney Enke

Reputation: 11

If you are trying to find all records with a temperature less than 3 you can use the following.

SELECT Email 
FROM Supplier 
WHERE Supplier_ID IN (SELECT Temperature FROM Batchofmeat WHERE Supplier.Supplier_ID = Batchofmeat.Supplier_ID AND Temperature < 3 );

Note, that this may not be performant with a large number of records.

Upvotes: 1

Related Questions