Details for Aravindh_sql

Aravindh_sql has participated in the following threads:

Added by Aravindh_sql on 29 May 2022 at 16:32

SELECT CountryName
FROM tblCountry INNER JOIN tblEvent
ON tblCountry.CountryID = tblEvent.CountryID
GROUP BY CountryName
HAVING COUNT(tblEvent.EventID) > 8

This Query Works fine.
Need not to write Sub-Query

View relevant page

Added by Aravindh_sql on 27 May 2022 at 11:27

SELECT COUNT(EventID) AS [Number of Events],
MAX(EventDate) AS [Last Date],
MIN(EventDate) AS [First Date]
FROM tblEvent

View relevant page

Added by Aravindh_sql on 27 May 2022 at 09:33

SELECT
tblCompanion.CompanionName AS Companion,
tblEpisode.EpisodeType
FROM tblCompanion
LEFT JOIN
tblEpisodeCompanion
ON
tblCompanion.CompanionId = tblEpisodeCompanion.CompanionId
LEFT JOIN
tblEpisode
ON
tblEpisodeCompanion.EpisodeId = tblEpisode.EpisodeId
WHERE tblEpisode.EpisodeType IS NULL

ANSWER

-- Companion= Sarah Jane Smith

View relevant page

Added by Aravindh_sql on 26 May 2022 at 18:27

SELECT CountryName ,
CASE
WHEN (ContinentID = 1 or ContinentID = 3 ) THEN 'Eurasia'

WHEN (ContinentID = 5 or ContinentID = 6 ) THEN 'Americas'
WHEN (ContinentID = 2 or ContinentID = 4 ) THEN 'Somewhere hot'
WHEN (ContinentID = 7 ) THEN 'Somewhere Cold'
ELSE 'Somewhere else'

END AS CountryLocation
FROM tblcountry
ORDER BY CountryLocation DESC

View relevant page

Added by Aravindh_sql on 26 May 2022 at 16:30

SELECT
CONCAT(EventName , '(', 'Catgeory',' ', CategoryID , ')') AS [Event(Category)] ,
EventDate
FROMtblEvent
WHERE CountryID = 1

View relevant page

Added by Aravindh_sql on 26 May 2022 at 07:42

SELECT Country,
KmSquared,
KmSquared/20761 as WalesUnits,
(KmSquared)-(KmSquared/20761)*20761 as AreaLeftOver,
CASE
       WHEN KmSquared < 20761 THEN 'Smaller than Wales'
ELSE
       CONCAT(
KmSquared/20761, 'x', ' Wales Plus ',' ' ,
(KmSquared)-(KmSquared/20761)*20761 ,' ','Sq.km'
)

END as WalesComparision

FROM CountriesByArea
ORDER BY ABS(20761-KmSquared) ASC

View relevant page

Added by sipi41 on 15 May 2022 at 21:43

The proposed solution was to sum the columns at the select part, then do the same when doing the WHERE. I was thinking about other solutions, but after carefully considering my solution: (where I encapsulated the result of the joins, then used the WHERE to filter) I'm starting to think this could create a very bad performance problem because it basically return all rows from the joins.

select * from (
   select
       aut.AuthorName, e.Title, doc.DoctorName, enemy.EnemyName,
       (
           len(aut.AuthorName) +
           len(e.Title) +
           len(doc.DoctorName) +
           len(enemy.EnemyName)
       ) AS [Total Length]
   from
   tblAuthor aut
   inner join tblEpisode e
       on e.AuthorId = aut.AuthorId
   inner join tblDoctor doc
       on doc.DoctorId = e.DoctorId
   inner join tblEpisodeEnemy epen
       on E.EpisodeId = EPEN.EpisodeId
   INNER JOIN tblEnemy enemy
       on epen.EnemyId = enemy.EnemyId
) AS r
WHERE
   r.[Total Length] < 40

My question is... would you please say if my solution is bad, and what would be the advantage of calculating during selection then repeating the calculation on the where again... thank you for all your help!

View relevant page