simple recursive query

Question

I have 2 tables as follows (sample data shown):

TableName: A
ID              Type
1  Bug
2  Requirement
3  Task
4  Specification
5  Bug
6  Specification
7  Production Issue
8  Production Issue
9  Bug
10          Task

Tablename: B
ID  RelatedID
1  2
1  7
5  8
5  4
9  6
9  10

I want to fetch all the bugs that have atleast one related production issue or bugs that have no related production issue.

Expected output will be as shown below (since these are the bugs with at least one related production issue)

output
1
5

Not very clear what you are looking for, perhaps include an example of your expected output? — Sparky
– Sparky, Commented Feb 23, 2010 at 21:29
i would refine how your tables connect together, as it stands now it is not very clear, what is related id, why are the primary keys for table b repeated and not auto incremented? — Jimmy
– Jimmy, Commented Feb 23, 2010 at 21:34
This is just a join, it isn't recursive in any way. If you wanted to enumerate all directly- and indirectly-related issues, that would be recursive. — Aaronaught
– Aaronaught, Commented Feb 23, 2010 at 21:40
Why is 9 omitted? I'm really confused what your algorithm is here. All three bugs have one Production Issue and one other kind of issue. They all seem to be in the same boat. This isn't really very clear. — David Morton
– David Morton, Commented Feb 23, 2010 at 21:43
@David My bad. I wanted to place 6 against 9. Instead I placed 7. So in the above case output will have 9. — stackoverflowuser
– stackoverflowuser, Commented Feb 23, 2010 at 21:56

Dancrumb · Accepted Answer · 2010-02-23 21:36:09Z

1

Aliases are the way to go here

SELECT pri.Type AS 'Primary Type', rel.Type AS 'Related Type' 
FROM A AS pri 
   INNER JOIN B ON B.ID = pri.ID
   INNER JOIN A AS rel ON B.RelatedID = rel.ID
WHERE pri.Type = 'Bug' AND rel.Type = 'Production Issue;

answered Feb 23, 2010 at 21:36

Dancrumb

27.7k10 gold badges79 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

simple recursive query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related