CODEWITHSUNDEEP
sql
jason dancks
jason dancks

Reputation: 1142

How would I do a yes/no type SQL query?

I just wanted to see a list of employees and a YES or NO next to their name if they are a supervisor. This is ANSI SQL not an implementation (I think thats what you call it). I was testing the commands at: http://edu.konagora.com/SQLsandbox.php#

Here's what I came up with:

SELECT firstname, lastname, CAST
(
   CASE
   WHEN Empl_ID IN (SELECT Empl_ID FROM Supervisor)
   THEN "YES"
   ELSE "NO"
   END AS varchar(3)
) AS 'ISS'
FROM Employee;

I get: (On Konagora.edu)

SQL Error: no such table: array

tables:

CREATE TABLE Supervisor
(
    Super_ID mediumint not null auto_increment,
    Empl_ID mediumint not null
);
CREATE TABLE Employee
(
    Empl_ID mediumint not null auto_increment,
    firstname varchar(100) not null,
    lastname varchar(100) not null,
    salary mediumint not null
);

Upvotes: 0

Views: 1719

Answers (1)

DRapp
DRapp

Reputation: 48139

aside from the other comment notes about single vs double quotes, you would be better doing a LEFT JOIN to the supervisor table .

SELECT 
      E.firstname, 
      E.lastname, 
      case when S.Empl_ID IS NULL then 'NO' else 'YES' end as Iss
   from
      Employee E
         LEFT JOIN Supervisor S
            on E.Empl_ID = S.Empl_ID

Upvotes: 2

Related Questions