Details for Orin

Hello, not sure if it is relevant to ask here but let's try : I do not understand fully how the Cast is working. Why my Test2 is not working, while only adding '/60' compared to the last Avg_track_length_mins ? :

I would add the precision "alphabetical Name order".
Otherwise some people - we won't give names - may go for ordering by IDs...

Before looking at the solution, I did not figure out it was possible to link the countries from Country and Event inside the correlation.

So I did the trick with a Distinct :

USE WorldEvents

SELECT Distinct
c.CountryName
-- ,count(e.EventID)
FROM
tblEvent as e
INNER JOIN tblCountry as c
ON e.CountryID = c.CountryID
WHERE
(SELECT count(e2.EventID) FROM tblEvent as E2
WHERE e.CountryID = e2.CountryID) > 8
--GROUP BY
-- c.CountryName

This one hurt! 5-6 tries and around 3-4 hours to break it. And I doubt I would have succeed without reading the answer 10 times.

To those who make it with only one JOIN + ISNULL : it is pretty, I did not think of it, but to me it is not the point of the exercise as this solution would be harder to scale up it there were more hierarchy levels.

---

To Wise Owl, if you have some 2 min for feedback ...

Short version: for the whole Join topic, I would recommend more, short but tricky, exercises, on very small tables to start with.

The "I have too much free time" version:
I made the mistake of not being 100% focused when I learned the Joins and/or not doing enough exercises right away.
I watched the video several times but somehow, it did not "print" correctly from the beginning. I learned too mechanically, without thinking it through, as it seemed obvious to link 2 identical fields in 2 different tables.
Coming from Excel, at first I saw it as a VLOOKUP giving back all the corresponding lines. No biggy.

I came back at it several times, redid the exercises with a clear mind, hit a wall when you threw a triple tables exercise in the View section. I leveled up when I found, and really studied.learned, pictures with the 7 Venn possible diagrams. And when I accepted they cannot be all done without using WHERE for some. Coming from Excel/VBA, it was not obvious to have to combine 2 instructions.

When I really tried this last one, I thought I was fully prepared. I had done my homework, I had run, I had sweat. I could hear Eyes of the tiger in my mind... and I failed miserably :-)) Why ?
a) I am not that smart and, again, was not focused enough + I tried to do it too fast although it is clearly a conceptual one.
b) It is less the Self part that threw me out rather than joining two fields that were not identical. I still have to digest this join "parent to kid" although the other way does lead to duplicate as each parent list its kid.
c) I do think I missed something by not learning & playing with smaller tables at the very beginning, and doing more small exercises.

For me, this exercise would be as conceptually hard with only 7-8 lines and/or with names embedding clear/familiar clues of the hierarchy (Granpa, Mom, Dad, Kid 1... or Big boss C level / Little bosses B level / Workers A level). It might be easier for some of us to get and read it.

And then, you can throw a second exercise with more lines or 4 hierarchy levels, or other variations... if there are any.

Right now I can't quite see something else than this "Bottom Up" Kids/Parents code approach.

A basic one would be a mirror version with a RIGHT OUTER. As I named my table 123, it ends looking more logical/making slightly more sense given the end-goal... although deep down, it is the same gymnastic mindset that I am not sure I would quickly replicate. I need to move on and create my own tables to redo it 30 times:

USE WorldEvents

SELECT

f1.FamilyName

,f2.FamilyName

,f3.FamilyName

,ISNULL(

ISNULL(f1.FamilyName + ' > ' + f2.FamilyName + ' > ' + f3.FamilyName,

f2.FamilyName + ' > ' + f3.FamilyName),

f3.FamilyName)

FROM tblFamily as f1

RIGHT OUTER JOIN tblFamily as f2

ON f1.FamilyID = f2.ParentFamilyId

RIGHT OUTER JOIN tblFamily as f3

ON f2.FamilyID = f3.ParentFamilyId

Figuring out what came wrong since my first attempt, using the provided beginning of the script ended in "Msg 111, Level 15, State 1, Line 3

'CREATE VIEW' must be the first statement in a query batch."

- your solution saved query contains ALTER instead of CREATE = once changed, it does create the view.
- we need to add AS after the view name, seems logical
- we finally need a GO after the Database name and at the end of the query.

Technically, we don't need the O to obtain your final picture ;-)

Hello, I can't seem to find out how to set a like filter in the Query designer. If I enter this (with or without ' ') :

I end up with WHERE (tblEpisode.EpisodeType = N'%special%')

I know how to correct it but I may have missed how to do it through this editor.

One solution with Format and Day :

format(EventDate, 'dddd d') + CASE WHEN day(EventDate) IN (1,21,31) THEN 'st ' WHEN day(EventDate) IN (2,22) THEN 'nd ' WHEN day(EventDate) IN (3,23) THEN 'rd ' ELSE 'th ' END + format(EventDate, 'MMMM yyyy') AS 'Full Date2'

How did you resist to the temptation of looking for this, that and the other ? #Seinfield Why is there HTML code in this screen ? And no preview mode ? So many questions, so many answers. Thanks.