Calculate and display in gridview

Question

0.00/5 (No votes)

See more:

In my asp.net+vb web with accessdatabase. Have query web page in which calculation is made.

VB

SELECT AUTH.company
    ,AUTH.Auth
    ,Count(AUTH.company) AS Held
    ,(AUTH.Auth - Count(AUTH.company)) as [Sur/Defi]
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth

The calculation is correct for those company in which persons are appointed

But if the person is not posted and even the company's auth is there then that names are not reflected in this web page. eg:_

SQL

Comapany            Auth     

Tata                12

and no one is posted in this company then this company name is not refelected

The company names should come even if there is no one posted in it as

SQL

Comapany            Auth         Held               Sur/Defi     

Tata                12             0                     12

and i want to add one more clause. There is a table named post and the field name in that is company (in this table transfers are entered).

i want one more calculation alongwith the above query is if transters are entered in post table then they should reflect as OUT in girdview .

My coding is in VB

Posted 7-Aug-13 4:09am

baijuep1970

Updated 7-Aug-13 17:29pm

Dholakiya Ankit

v2

Add a Solution

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)

woopsydoozy · Answer 1 · 2013-08-07T05:14:00

Solution 1

If I understand you correctly, all you need to do is switch your JOIN:

SQL

SELECT AUTH.company
    ,AUTH.Auth
    ,Count(AUTH.company) AS Held
    ,(AUTH.Auth - Count(AUTH.company)) as [Sur/Defi]
FROM AUTH LEFT OUTER JOIN pers ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth

I don't quite understand the part about the transfers, need more info.

Posted 7-Aug-13 5:14am

woopsydoozy

Comments

baijuep1970 7-Aug-13 12:36pm

There is one more table (Post) i want to join that table also to this query. Count(pers.company=post.company) as out
but i am not able to join it
the logic is all unit from Auth.unit should display in my gridview

woopsydoozy 7-Aug-13 13:07pm

I'm still not getting it. You seem to need some basic SQL training--try http://www.w3schools.com/sql/default.asp, and get into your DB manager and just keep tweaking your query until you get the result you want. Sounds like you're looking for another JOIN, likely something like this: AUTH LEFT OUTER JOIN pers ON pers.company = AUTH.company LEFT OUTER JOIN Post ON pers.company = Post.company. The data you want from the post table, I still don't see--are you trying to get a count of the people at that company, or the number of transfers, or ...? And where does unit come in?