SQL UNION with conditions and recursive

Question

Hi, i'm having problems with 2 query for my DB class.

I have one table "clients" with this columns:

name   | id     | date     | points | REFERRAL
---------------------------------------------
daniel | 123456 | 01-01-01 | 50     | 321321
jack   | 321321 | 01-01-01 | 30     | 555555

Note that daniel was refered by jack

1) With the ID of one person show all client referrals and referrals from them to a third reference level

First i think on something like this, just for a reference level of 2:

SELECT C1.name
FROM Clients C1, Clients C2
WHERE C1.REFERRAL= 21000301
      AND C1.id= C2.REFERRAL

This obviously not work for the AND. Then I try UNION:

SELECT C1.id, C1.REFERRAL
FROM Clients C1
WHERE C1.REFERRAL= 21000301

UNION

SELECT C2.id, C2.REFERRAL
FROM Clients C2
WHERE C2.REFERRAL= C1.ID

But can't access C1 on the second SELECT

So, my question is there is a way to make conditions with the UNION. If not, how can i solve this?

2) Show the name and the total number of referrals to the client with more direct and indirect referrals.

On this one I'm completely lost :D, May be it is recursive? Is there something like that in SQL?

Thanks in advance, and sorry my poor English.

Answer 1

The reason your first example doens't work is the c1.referral = 21000301: there is no row like that. If you omit that, the query works:

SELECT  C1.name
FROM    Clients C1, Clients C2
WHERE   C1.id = C2.REFERRAL

Or rewritten more clearly as a join for two levels:

SELECT  C1.name
FROM    Clients C1
JOIN    Clients C2
ON      C1.id = C2.REFERRAL
JOIN    Clients C3
ON      C2.id = C3.REFERRAL

You'd only need a recursive query for an arbitrary level of referrals. Recursive query syntax differs per database, so you'd have to tell us which database you are using.

Answer 2

Im using: c1.referral = 21000301 because i need the referrals of one person given the ID and that ID is on the table, just for testing.

I try this:

SELECT  C1.name
FROM    Clients C1
JOIN    Clients C2
ON      C1.id = C2.REFERRAL
JOIN    Clients C3
ON      C2.id = C3.REFERRAL
WHERE   c1.referral = 21000301

But it only shows one name 8 times.

And about the database i really don't know, my college is doing the hosting, I'm using Oracle SQL Developer if helps. Anyway I guess any code will help me.

Thanks!

Answer 3

I figure it out :D

select c.name
from CLIENTS C
where c.REFERRAL=21000301 OR c.REFERRAL
   IN(SELECT c.id FROM CLIENTS C WHERE c.REFERRAL=21000301 OR c.REFERRAL
   IN(SELECT c.id FROM CLIENTS C WHERE c.REFERRAL=21000301 OR c.REFERRAL
   IN(SELECT c.id FROM CLIENTS C WHERE c.REFERRAL=21000301)))