Something simple I don't see

Question

Hi everybody,

Please look at these two queries:

SELECT RTRIM(T.Descrip) + ' - ' + V.Descrip AS Descrip
	,T.TemplateId
FROM dbo.rsTemplates T
INNER JOIN dbo.rsVenues V ON T.VenueID = V.VenueID
WHERE EXISTS (
		SELECT 1
		FROM dbo.rsLevels L
		INNER JOIN dbo.rsSections S ON L.LevelID = S.SectionID
		INNER JOIN dbo.rsTSBlocks B ON B.LevelID = S.LevelID
			AND B.SectionID = S.SectionID
		WHERE L.TemplateId = T.TemplateId
		)
ORDER BY Descrip

select * from dbo.rsLevels L
inner join dbo.rsSections S ON L.LevelID = S.LevelID
inner join dbo.rsTSBlocks B ON B.LevelID = S.LevelID
AND B.SectionID = S.SectionID
inner join dbo.rsTemplates T ON L.TemplateID = T.TemplateID
INNER JOIN dbo.rsVenues V ON T.VenueID = V.VenueID
where L.TemplateID = 53

The idea of the first query is to check, that the templates we're selecting have corresponding levels, sections and blocks. If it doesn't have levels, sections or blocks defined, I do not want to select such templates.

Somehow, that query is too restrictive and the template 53 which does produce rows (see second query) is not shown in the first result. So, something is wrong in my logic and I should re-structure this query differently.

Do you see what is my mistake here and what will be the right way to re-structure the query? I'm going to try with IN subquery now.

UPDATE. In subquery produced the same result. I'll turn this into JOIN although it's weird.

Thanks in advance.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 1

Ok, never mind. Sometimes writing the same query 5 times in different ways helps. After I got 0 results even using JOIN I started to suspect I made a mistake.

And indeed I did - try to find it.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

L.LevelID = S.SectionID ? : )

Answer 3

Yes. BTW, it's not the first time I made a similar mistake. But somehow it's always hard to find.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Ah easy to do.  At least you found it by checking it with another query - which is a good way of testing them.  The worst ones are the ones you don't find!