Add one subquery to existing query to get the percentage from another table

Question

1.00/5 (1 vote)

See more:

i am working on multi level marketing website binary tree based.Below query is returning me total number of left side and right side members. Below query is working fine. Now i want to add one more condition in the below query. That basically will be the sub query. i am not familiar with the sub queries or nested queries. so i want help from you guys.

cmd = new SqlCommand("declare @nodeid int = '"+cust_id+ "';with cte as (select cust_id, parentid, joinside, null left_node, null right_node from  user_detail where cust_id = @nodeid union all select t.cust_id, t.parentid, t.joinside,ISNULL(cte.left_node, CASE WHEN t.joinside = 0 THEN 1 ELSE 0 END) left_node,ISNULL(cte.right_node, CASE WHEN t.joinside = 1 THEN 1 ELSE 0 END) right_node from  user_detail t inner join cte on cte.cust_id = t.parentid ) select @nodeid nodeid, SUM(left_node) LeftNodes,SUM(right_node) RightNmodes from cte option (maxrecursion 0)", con);

What i want is: i have another table installments in which there is a flag column which contains values "paid" and "not paid". i want to count %age of left side and right side customers who have paid the installments of specific id. i want this condition in the above query. i hope you guys understand my question. Thanks

What I have tried:

i have not tried anything yet.. i am not familiar with nested or sub queries.

Posted 23-Dec-18 4:39am

Harpreet_125

Updated 4-Jan-19 8:34am

Add a Solution

Comments

Wendelius 23-Dec-18 12:27pm

Could yo post the table definitions for all the tables participating in the query. That would help to understand the situation

Maciej Los 24-Dec-18 3:31am

Note, that your sql command is sql injection vulnerable!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dbrenth · Answer 1 · 2019-01-04T08:35:00

Without seeing your table structure, I can only guess what you want.

A simple subquery for what you want would be included in your WHERE clause and might look like this:

WHERE ...<existing clause>...
AND cte.InstallmentId in (Select InstallmentId from Installments where flag = 'paid')

Now that subquery can be as simple or as complex as you need it to be, but it should return a list of a single column that you can query with an "IN" clause.

If you need multiple columns, you can also JOIN to a subquery.

In your table section you can do

FROM ...<existing from clause>...
LEFT OUTER JOIN (Select InstallmentId, Age from Installments where flag = 'paid') as I on cte.InstallmentID = I.InstallmentID and cte.Age = I.Age

Hopefully that is enough to get you going.

Brent