How to get all Contract no against Leads in oracle sql query?

Question

I need to create a sql query for below scenario:

Table name is remark

Columns are contractno and leadid.

1 contractno can have multiple leadid. similarly, 1 leadid can assigned to multiple contractno.

Lets assume:
C1 --> L1
C2 --> L1, L2
C3 --> L2

I will get only one contractno i.e. C1 as parameter.

Now I have to find all Contracts against C1 through leadid.

Please help me out how I can achieve this. Thank you.

`I have to find all Contracts against C1 through leadid.` what that mean? Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Try create a sample in http://rextester.com — Juan Carlos Oropeza, Oct 23 '17 at 14:43
Add some sample table data and the expected result, as formatted text. Also show us your current query attempt. — jarlh, Oct 23 '17 at 14:45
@Velerica I think that is the opposite example. He need csv field into multiple rows — Juan Carlos Oropeza, Oct 23 '17 at 14:50

Juan Carlos Oropeza · Answer 1 · 2017-10-23T14:51:14.193

1

SELECT r1.contractno
FROM remark r1
JOIN remark r2
  ON r1.leadid = r2.leadid
WHERE r2.contractno  = 'C1'
  AND r1.contractno <> 'C1'

This assume your table has this format:

contractno leadid
 C1        L1
 C2        L1
 C2        L2
 C3        L1

If you dont, then you need to split the csv value into rows first:

Turning a Comma Separated string into individual rows

edited Oct 23 '17 at 14:51

answered Oct 23 '17 at 14:46

Juan Carlos Oropeza

47,252
12
78
118

This might help me, will test and check it here as correct. – Aamir Oct 23 '17 at 15:07
Please use L2 for C3, remove L1 for C3 and let me know if works. – Aamir Oct 23 '17 at 15:49
Looks like you change your question? Maybe you need a recursive cte to find all the project related to C1 and not just the direct linked to C1? – Juan Carlos Oropeza Oct 23 '17 at 17:58
yes all project related to C1 is required. Can you please suggest how I can perform recursive cte? – Aamir Oct 24 '17 at 09:24

score 0 · Answer 2 · answered Oct 23 '17 at 15:37

0

You can use a LISTAGG if you have to group list of contracts. Here too it is assumed that your table has linear format and not comma separated leadids

   WITH cn
         AS (SELECT DISTINCT leadid
             FROM   remark
             WHERE  contractno = 'C1')
    SELECT Listagg(r.contractno, ',')
             within GROUP (ORDER BY ROWNUM) contractno_C1
    FROM   remark r
           join cn
             ON r.leadid = cn.leadid
    WHERE  r.contractno <> 'C1'
    GROUP  BY cn.leadid;

http://sqlfiddle.com/#!4/54e48/1/0

answered Oct 23 '17 at 15:37

Kaushik Nayak

30,772
5
32
45

Kaushik, Could you please use below values and run, its not giving required ouput, Also I want to show C1 as well: INSERT ALL INTO test1 (contractno, leadid) VALUES ('C1', 'L1') INTO test1 (contractno, leadid) VALUES ('C2', 'L1') INTO test1 (contractno, leadid) VALUES ('C2', 'L2') INTO test1 (contractno, leadid) VALUES ('C3', 'L2') SELECT * FROM dual ; – Aamir Oct 23 '17 at 15:52
yes its not right. We must have C3 also because C3 have L2 and L2 is in C2 which also have L1(under C1), So its a chain and C3 is required. – Aamir Oct 23 '17 at 16:00
But you mentioned in the question "I will get only one contractno i.e. C1. " – Kaushik Nayak Oct 23 '17 at 16:02
C1 getting means as parameter. Let me update the question – Aamir Oct 23 '17 at 16:02

How to get all Contract no against Leads in oracle sql query?

2 Answers2