CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ONYX
ONYX

Reputation: 5859

select statement where clause two conditions

I'm having a problem with my select statement at the end. What I want to do is select two levels but im getting no results with this statement. note that if @Level is 1 I don't want to go less than 1 or it might mess up the result so there might be a condition aswell

the part where it goes @Level = @Level -1 I'm having problems with

FROM c WHERE c.Level = @Level AND c.Level = @Level - 1 ORDER BY Level ASC

or this one

FROM c WHERE c.Level BETWEEN @Level AND c.Level - 1 ORDER BY Level ASC

SQL

DECLARE @Department TABLE
(
Id INT NOT NULL,
Name varchar(50) NOT NULL,
ParentId int NULL
)

INSERT INTO @Department SELECT 1, 'Toys', null
INSERT INTO @Department SELECT 2, 'Computers', null,  
INSERT INTO @Department SELECT 3, 'Consoles', 2
INSERT INTO @Department SELECT 4, 'PlayStation 3', 3
INSERT INTO @Department SELECT 5, 'Xbox 360', 2
INSERT INTO @Department SELECT 6, 'Games', 1
INSERT INTO @Department SELECT 7, 'Puzzles', 6
INSERT INTO @Department SELECT 8, 'Mens Wear', null
INSERT INTO @Department SELECT 9, 'Mens Clothing', 8
INSERT INTO @Department SELECT 10, 'Jackets', 9
INSERT INTO @Department SELECT 11, 'Shoes', 9
INSERT INTO @Department SELECT 12, 'Rain Coats', 10

DECLARE  @Level int = 4
;WITH c AS
(
SELECT Id, ParentId, Name, 1 AS Level
FROM @Department
WHERE ParentId IS NULL

UNION ALL

SELECT t.Id, t.ParentId, T.Name, c.Level + 1 AS Level
FROM @Department T
INNER JOIN c on t.ParentId = c.Id   
)
SELECT Id, ParentId, Name, Level

FROM c WHERE c.Level = @Level AND c.Level = @Level - 1 ORDER BY Level ASC

FROM c WHERE c.Level BETWEEN @Level AND @Level - 1 ORDER BY Level ASC

Upvotes: 2

Views: 206

Answers (4)

JBrooks
JBrooks

Reputation: 10013

Have a look at the SQL below, it comes back with 4 rows for level 3 and 1 for level 4:

SELECT p.Id as ParentId, 
    p.Name as Parent,
    c.Id, 
    c.Name, 
    c.Level
FROM c as c
left join c p 
on p.Id = c.ParentId
WHERE c.Level = @Level 
ORDER BY p.Name, c.Name ASC

Upvotes: 0

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

Okay, your recursive CTE appears to be right on. I normally wouldn't put name in the CTE (mostly because either the table is only the parent/child fields, or has too many), and join afterwards, but it should still work just fine.

As has been pointed out, your real problem is that your where clauses are non-sensical:

  • WHERE c.Level = @Level AND c.Level = @Level - 1 basically asks whether c.level == c.Level - 1 - which is always false.
  • WHERE c.Level BETWEEN @Level AND @Level - 1 is asking for @Level >= c.Level >= @Level - 1 or @Level >= @Level - 1 - another impossible condition.

Either one of these approaches should work just fine, if slightly modified:

  • WHERE c.Level = @Level OR c.Level = @Level - 1
  • WHERE c.Level BETWEEN @Level - 1 AND @Level

(it doesn't matter if @Level is input as 1 - because you start at one, there's no lower level for it to report).

Upvotes: 0

Lamak
Lamak

Reputation: 70648

Maybe FROM c WHERE c.Level BETWEEN @Level -1 AND @Level ORDER BY Level ASC

Upvotes: 2

Timothy Khouri
Timothy Khouri

Reputation: 31845

This code here is NEVER able to work:

@Level = @Level - 1

I think you meant to say:

c.Level = @Level - 1

And even at that... you probably want an OR instead of an AND.

Think about what you're asking... "give me all the records where the level is 4 and 5 at the same time."

What you mean to say is probably... "give me all the records where the level is 4 OR 5."

Upvotes: 1

Related Questions