How do I write nested SQL query.

Question

0.00/5 (No votes)

See more:

Hello All,

Firstly am relatively new to this SQL and learning step by step.
Well, am trying to mimic an Excel function into a SQL query, which has a nested sub query.

Example:

Table1

Name  ||  ID  
XYZ   ||  12  
ABC   ||  13  
VDS   ||  14

Table2

Bank_Approved  ||  ID  ||  Certified  
NULL           ||  12  ||  No Value  
1234           ||  13  ||  No Value
1245           ||  14  ||  Yes

Here, Table1 has Name and ID which is linked to the Table2 using Inner JOIN.
Now, i need the resultant Table as follows:

Table3

Name  ||  Bank_Cert
VDS   ||  1

The Bank_Cert result should be "1" since Bank Approved has "1245" and it is Certified.

Excel Function:

COUNTIFS('Table View  1'!BX:BX, ">0", 'Table View  1'!C:C,C5, 'Table View  1'!CG:CG,  "Yes")

What I have tried:

(SELECT Count(case when [Bank_Approved] IS NOT NULL then 1 end)FROM Table2 WHERE [Certified] = 'Yes' group by Table1.[Name] ) as Bank_Cert

Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

Posted 15-Mar-16 3:59am

Member 10376341

Updated 15-Mar-16 4:19am

Add a Solution

Comments

ZurdoDev 15-Mar-16 10:07am

What exactly do you want? Don't explain it using Excel, just simple English.

Member 10376341 15-Mar-16 10:15am

Well i have Name, ID, Bank Approved, Certified columns in two tables. Based on the two am extracting a result, where the result should be bind to two rules
1. Bank should be approved.
2. It should be Certified.

Please, refer the example tables.

Kornfeld Eliyahu Peter 15-Mar-16 10:07am

Why nested? Simple JOIN can do that...

Member 10376341 15-Mar-16 10:10am

Thank you for the reply. Can you give me an example?

Kornfeld Eliyahu Peter 15-Mar-16 10:13am

SELECT
TABLE1.NAME,
CASE WHEN ISNULL(TABLE2.CERTIFIED, '') = 'YES' THEN 1 ELSE 0 END
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE2.ID = TABLE1.ID

Member 10376341 15-Mar-16 10:20am

The result shows zero for all Names.

Kornfeld Eliyahu Peter 15-Mar-16 10:31am

Probably Certified's value not 'yes'...

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)

ZurdoDev · Accepted Answer · 2016-03-15T04:19:00

Solution 1

This should give you a list of all approved and certified banks:

SQL

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Certified = 'Yes' AND t2.[Bank Approved] IS NOT NULL

Table1 and Table2 "share" a column, ID. The id is the join between the two tables. After you join your tables then you only want the records that have Certified = 'Yes' and Bank Approved is not null.

Posted 15-Mar-16 4:19am

ZurdoDev

Comments

Member 10376341 15-Mar-16 10:30am

Thank you for the reply, well it gives me all the data present in the respective tables. I need the resultant data to be segregated with the name.

ZurdoDev 15-Mar-16 10:33am

Then just do t1.Name, etc. Pick whatever fields you like.

Member 10376341 15-Mar-16 10:38am

Yes we got the result. But how do i count it, in a table there will be similar name and ID.
It will be nice if you can throw a light on my query.

(SELECT Count(case when [Bank_Approved] IS NOT NULL then 1 end)FROM Table2 WHERE [Certified] = 'Yes' group by Table1.[Name] ) as Bank_Cert

ZurdoDev 15-Mar-16 10:39am

If you just want a count, do SELECT COUNT(*) FROM ...

Member 10376341 15-Mar-16 10:40am

Am trying to count all the records which are relevant to the particular Name and ID

ZurdoDev 15-Mar-16 10:43am

What should your output look like? And explain why?

Member 10376341 15-Mar-16 10:42am

Thank you got the result.
Thank you so much for your time.

Member 10376341 15-Mar-16 10:45am

One more thing i wanted to ask you. How do i save it with the column name?
Now it shows the result as (No Column Name).

ZurdoDev 15-Mar-16 10:48am

AS WhateverYouWant.

SELECT COUNT(*) AS TotalCount -- for example.

Member 10376341 15-Mar-16 10:49am

Thank you so much. Appreciate your help.

Regards

ZurdoDev 15-Mar-16 11:04am

You're welcome.