MS SQL select statement with "check table"

Question

I'm struggling to find out a solution for my select statement. I have two tables:

CREATE TABLE Things
(
  Id int, 
  type int
)

CREATE TABLE Relations
(
  Id int,
  IdParent int,
  IdChild int
)

What I need to select, based on given Thing Id:

All records from Things that has Type = -1
All records from Things that has Type matching IdChild where IdParent is Type of a row matching given Id.
If Type of a row matching given Id is -1 or doesn't exist in table Relations (as IdParent) I need to select all records from Things

I am having problem with the last scenario, I tried to do that by joining Relations table, but I can't come up with a condition that would satisfy all scenarios, any suggestions?

UPDATE

This is how I do it now. I need to solve the scenario where given id does not exists in table Relations - then I need to select all records.

CREATE TABLE Things (id int, type int)
CREATE TABLE Relations (id int, parent int, child int)

INSERT INTO Things VALUES (1, 1)
INSERT INTO Things VALUES (2, -1)
INSERT INTO Things VALUES (3, 3)
INSERT INTO Things VALUES (4, 3)
INSERT INTO Things VALUES (5, 2)
INSERT INTO Things VALUES (6, -1)

INSERT INTO Relations VALUES (1, 1, 2)

DECLARE @id int = 1

SELECT * FROM Things
JOIN Relations R ON R.parent = @id AND Type = R.child OR Type = -1

So I must solve the situation where @id = 2 for example - I need to retrieve all rows (same for @id = 5, unless it appears in Relations in parent column somewhere).

UPDATE 2

I came up with something like that:

DECLARE @id int = 2
DECLARE @type int

SELECT @type = type FROM Things WHERE Id = @id

IF @type > -1
BEGIN
SELECT T.* FROM Things T
JOIN Relations R ON (R.parent = @id AND T.Type = R.child) OR T.Type = -1
END
ELSE
BEGIN
SELECT * FROM Things
END

I'm quite sure that this can be done differently, without conditional IF, optimized.

Can you provide an data example of your raw data and what you like to receive? I'm promise you get a working query back ;) — Ionic, Jun 15 '15 at 13:49
is this the kind of thing you're after, for retrieving a parent and it's children: http://stackoverflow.com/a/25551203/57475 — Tanner, Jun 15 '15 at 14:18

score 0 · Answer 1 · answered Jun 15 '15 at 14:49

0

 from Things
 left outer join Relations 
   on Relations.IdParent = Things.Type 
where Things.Type = -1 or Relations.IdParent is null

answered Jun 15 '15 at 14:49

paparazzo

44,497
23
105
176

MS SQL select statement with "check table"

1 Answers1