Potential Recursive SQL Query

Question

I have a three-level SQL table tree; parent, child, orphan child.

I wish to select one row from parent i.e. parent key "ID"=3

Then select all rows in table1 child where child1 key "ID1"=3 IF....

Orphan child table2 is a list of "ID2" which act as a filter for "ID1"

I have attempted a left join:

SELECT [itemName]
FROM [dbo].[table]
WHERE [ID] = 3
LEFT OUTER JOIN [dbo].[table1]
    ON [dbo].[table].[ID] = [dbo].[table1].[ID1]

The where is breaking it and I'm not even close to figuring out the 3rd level filtering of table1.

These are my table definitions.

CREATE TABLE [dbo].[table] (
    [ID]  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [Company]   varchar(250)
);

CREATE TABLE [dbo].[table1] (
    [t1_fid]  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [Product]   varchar(250),
    [Description]   varchar(250),
    [ID1] INT FOREIGN KEY REFERENCES [dbo].[table]([ID]) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE [dbo].[table2] (
    [t2_fid]  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [someValue]  INT,
    [ID2] INT FOREIGN KEY REFERENCES [dbo].[table]([t1_fid]) ON UPDATE CASCADE ON DELETE CASCADE
);

My tables are as such.

table:
[ID]    [Company]
1       companyA
2       companyB
3       companyC

table1:
[t1_fid]  [Product]   [Description]   [ID1]
1         Tree        Green           3
2         House       Built           1
3         Tree        Dead            3
4         Car         White           2
5         House       Sold            3
6         Car         Crashed         3
7         Car         Sold            3

table2:
[t2_fid]    [someValue]   [ID2]
1           60            1
2           2             2
3           15            5
4           0             6

So here is the result table I would like. It is a table of all companyC Products which also exist in table2.



[Company]   [Product]   [Description]   [someValue]
companyC    Tree        Green           60
companyC    House       Sold            15
companyC    Car         Crashed         0

I hope this enough information!

Laughing Vergil · Accepted Answer · 2019-07-18 16:27:22Z

1

Your query has a basic syntax error. This:

SELECT [itemName]
FROM [dbo].[table]
WHERE [ID] = 3
LEFT OUTER JOIN [dbo].[table1]
    ON [dbo].[table].[ID] = [dbo].[table1].[ID1]

Should be this:

SELECT [itemName]
FROM [dbo].[table] 
LEFT OUTER JOIN [dbo].[table1]
    ON [dbo].[table].[ID] = [dbo].[table1].[ID1]
WHERE [ID] = 3

answered Jul 18, 2019 at 16:27

Laughing Vergil

3,7661 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PJ99 Over a year ago

Great thanks, now how do I add my second level filtering?

Laughing Vergil Over a year ago

Since you only want entries from table1 where a matching entry exists in table2 an INNER JOIN between table1 and table2 should do what you are looking for. Include it before the WHERE clause but after the LEFT OUTER JOIN

Error_2646 · Accepted Answer · 2019-07-18 16:53:50Z

0

You are pretty close. I don't see why recursion is necessary though, if I'm understanding right.

SELECT *
FROM table tbl
INNER JOIN table1 t1
    ON tbl.ID = t1.ID1
WHERE tbl.id = 3
  AND EXISTS
        ( SELECT 1
            FROM table2 t2
           WHERE t1.t1_fid = t2.id2
        )

answered Jul 18, 2019 at 16:53

Error_2646

3,8991 gold badge17 silver badges26 bronze badges

2 Comments

PJ99 Over a year ago

That worked very well right out of the box! Thanks so much.

PJ99 Over a year ago

Rather than a SELECT *, is it possible to select only some of the columns from all three tables? I notice the above didn't return any data from table2. Can it be modified to that?

Thorsten Kettner · Accepted Answer · 2019-07-18 17:18:45Z

0

This is very basic. You want to see data from all tables and you only want matches. This is what simple inner joins do.

SELECT
  t.company,
  t1.product,
  t1.description,
  t2.somevalue
FROM dbo.[table] t
JOIN dbo.table1 t1 ON t1.id1 = t.id
JOIN dbo.table2 t2 ON t2.id2 = t1.t1_fid
WHERE t.id = 3;

(JOIN is short for INNER JOIN.)

answered Jul 18, 2019 at 17:18

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Potential Recursive SQL Query

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related