How do I use a correlated sub query for a new column in my view?

Question

Hi, I am trying to write a view that has 3 columns: Planet, Moon, and Largest.

The view is meant to show planets, their moons, and a Yes or No column indicating whether or not it is the largest moon for the planet.

Only one Basetable is used, and the columns I am referencing are moonPlanetOrbit (only not null if bodyType is = to 'Moon'), bodyName (name of the moon), and largest ('yes' or 'no').

Here is my attempt so far:

CREATE VIEW Moons (Planet, Moon, Largest)
select moonPlanetOrbited, bodyName, ('Yes' if bodyName = (SELECT top 1 moonMeanRadius from Body where moonPlanetOrbited = bodyName order by moonMeanRadius) as Largest)

I can provide any more information if needed.

Thanks, Cody

Answer 1

Here is my MS-SQL Syntax stab at it:

SELECT
  B.moonPlanetOrbited
  , B.bodyName
  , CASE
      WHEN B.bodyName = 
      (SELECT TOP 1
         iB.bodyName
       FROM
         Body AS iB
       WHERE
         iB.moonPlanetOrbited = B.bodyName
       ORDER BY
         iB.moonMeanRadius DESC
       )
       THEN 'Yes'
       ELSE 'No'
     END CASE AS [Largest]
FROM
 Body AS B

If the table uses IDs as a primary key it may be better to compare the IDs instead of the names.

Answer 2

SQL works best with sets of data. My advice is to get the set of largest moons using a SELECT statement and the MAX() function, and then join the result set with the whole table. Then test whether the moon is equal to the largest in order to print 'yes' or 'no'.

Here's an example using MySQL. I created a table Moons containing the columns moonPlanetOrbited, bodyName, moonMeanRadius. The following SQL selects the largest moonMeanRadius for a given moonPlanetOrbited:

SELECT moonPlantedOrbited, MAX(moonMeanRadius) as maxMoonRadius
FROM Moons
GROUP BY moonPlanetOrbitede

Now that we have a list of maxMoonRadius, join the result set with the entire table and test if the moonMeanRadius is equal to the maxMoonRadius:

SELECT m1.moonPlanetOrbited, m2.bodyName,  
if(m1.moonMeanRadius = m2.maxMoonRadius, 'Yes', 'No') as Largest 
FROM Moons m1  
JOIN (   
  SELECT moonPlanetOrbited, MAX(moonMeanRadius) as maxMoonRadius   
  FROM Moons   
  GROUP BY moonPlanetOrbited 
) m2 
ON m1.moonPlanetOrbited = m2.moonPlanetOrbited;

The IF syntax is from MySQL 5.5: http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#function_if

Tested using the following SQL :

CREATE TABLE Moons( 
  moonPlanetOrbited VARCHAR(255), 
  bodyName VARCHAR(255), 
  moonMeanRadius FLOAT
);

INSERT INTO Moons('a', 'b', 1.01);
INSERT INTO Moons('a', 'c', 1.02);
INSERT INTO Moons('a', 'd', 1.03);
INSERT INTO Moons('a', 'e', 1.04);


+-------------------+----------+---------+
| moonPlanetOrbited | bodyName | Largest |
+-------------------+----------+---------+
| a                 | b        | No      |
| a                 | c        | No      |
| a                 | d        | No      |
| a                 | e        | Yes     |
+-------------------+----------+---------+
4 rows in set (0.00 sec)

Answer 3

Here is an attempt (untested) that resembles your approach as closely as possible, since your idea wasn't that far off:

Select 
M.moonPlanetOrbited, 
M.bodyName, 
CASE
  WHEN M.bodyName = 
    (SELECT top 1 bodyName from Body 
    where moonPlanetOrbited = M.moonPlanetOrbited 
    order by moonMeanRadius DESC) 
Then 'Y' 
Else 'N'
AS Largest
FROM body

You just needed a table prefix to actually do the correlating to the root table, and also to make sure that you were comparing apples to apples in your CASE statement.