i want data from 2 tables in one query ?

Question

1.00/5 (1 vote)

See more:

i have 2 tables both contain almost same field the 1st table monthly is like the current expenditure table and the 2nd table yearly contains yearly allocated amount for monthly table columns. what i m trying to achieve is i want the sum of all the first table columns and also i want to show the contents of 2nd table.

expected output:

Heads        || budget allocation during year ||  total expenditure ||  balance amount
salary           20000                            18000               
ta               10000                            1000
contingency      3000                             2000
NRC              1000                              500
institcharges    100000

others

now in footer || total of all this         ||   total of this    ||  allocation-expenditure

now i just need the query to work i have made the layout and footer thing to work i just need to group by the contents of 1st table and just display contents of other

"select m.pcode, m.fyyear,sum(m.salary),sum(m.ta),sum(m.contigency),sum(m.nrc), sum(m.institcharges),sum(m.others),y.yearlyalloc,y.salary as sal1,y.ta as ta1,y.contigency as cont1, y.nrc as nrc1,y.institcharges as inst1,y.others as other1 FROM monthly AS m , yearly as y where m.pcode=y.pcode and m.pcode=('" + DropDownList1.SelectedItem.Value.ToString() + "' ) AND y.fyyear like('" + DropDownList2.SelectedItem.Value.ToString() + "') and m.pcode, m.fyyear= y.fyyear group by m.pcode, m.fyyear", con

this is the query i am working with but its apparently wrong.i need a way to add all columns of 1st table and just display the columns of 2nd table

Posted 7-Apr-13 7:36am

a2ulthakur

Updated 7-Apr-13 7:44am

Maciej Los

v3

Add a Solution

Comments

Prasad Khandekar 7-Apr-13 15:29pm

Please read the Documentation on LEFT JOIN.

a2ulthakur 7-Apr-13 21:57pm

how is it helpful??

2 solutions

Solution 2

This is accomplished using the SQL keyword JOIN

See article[^] explaining the usage of JOIN.

Good luck,
Edo

Posted 7-Apr-13 19:19pm

Joezer BH

Comments

a2ulthakur 8-Apr-13 8:43am

string str = string.Format(@"select m.pcode, m.fyyear,sum(m.salary),sum(m.ta),sum(m.contigency),sum(m.nrc),sum(m.institcharges),sum(m.others) FROM monthly m where pcode=('" + DropDownList1.SelectedItem.Value.ToString() + "' ) AND fyyear like('" + DropDownList2.SelectedItem.Value.ToString() + "') group by pcode, fyyear UNION select y.pcode,y.fyyear as fyear,y.yearlyalloc as yalloc,y.salary as ysalary,y.ta as yta,y.nrc as ynrc,y.institcharges as yinstit,y.others as yother from yearly y where y.pcode=('" + DropDownList1.SelectedItem.Value.ToString() + "' ) and y.fyyear like('" + DropDownList2.SelectedItem.Value.ToString() + "')", con);
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dtNew = new DataTable();
da.Fill(dtNew);
GridView1.DataSource = dtNew;
GridView1.DataBind();

Joezer BH 8-Apr-13 9:22am

The way to JOIN two tables is only if they have a Relation.
If they do have a Relation then filed a in table T1 has the same meaning as field b in table T2.

If that is the case in your situation (perhaps you wish to join table MONTHLY and table YEARLY, just a guess) then you would use the JOIN:

SELECT column_name(s)
FROM T1
INNER JOIN T2
ON T1.a = T2.b

a2ulthakur 8-Apr-13 9:28am

i know about joins but my problem is not so simple i need the first table's columns sum and them grouped by according to project code and financial year and in the 2nd table i need just to display all the columns of that table and all this has to be a datasource of gridview in which i have to display all this.

Joezer BH 9-Apr-13 4:44am

It's a bit unclear what you wish to show and what is the relation between your two tables

a2ulthakur 10-Apr-13 4:28am

http://stackoverflow.com/questions/15920651/how-to-use-group-by-in-a-join-query-where-i-in-one-table-i-have-to-get-the-sum-o

i hope this link makes my question bit more clear

Joezer BH 10-Apr-13 5:00am

Did you try doing:

SELECT [your select fields here...] FROM (SELECT ... the data form monthly here with the suns) AS A INNER JOIN (SELECT ... the data from yearly here ...) AS B on A.fyYear = B.fyYear

a2ulthakur 10-Apr-13 5:33am

i did tried it by making a join for getting columns for both tables then in the gridview i inserted labels in column's item template and under gridview RowDataBound event i tried this:

if (e.Row.RowType == DataControlRowType.DataRow)
{
t1 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "salary"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbsal = (Label)e.Row.FindControl("lbsal");
lbsal.Text = t1.ToString();
}

i used this method to get sum in my gridview footer so i tried the same approach here but the problem with this approach is for both the financial years 2010-2011 and 2011-2012 the total of columns of monthly table is remaining the same but yearly allocation is shown correctly.

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)

a2ulthakur · Accepted Answer · 2013-04-10T21:09:00

SQL

string str = string.Format(@"select m.pcode,sum( m.salary) as SalaryConsumed, y.salary,sum(m.ta) as taConsumed,y.ta,sum(m.contigency) as contiused, y.contigency,sum(m.nrc) as nrcconsumed,y.nrc,sum(m.institcharges) as institgiven,y.institcharges,sum(m.others) as miscused,y.others, m.fyyear,y.yearlyalloc from monthly m
        inner join yearly y on m.pcode = y.pcode and m.fyyear = y.fyyear where m.pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND m.fyyear=('" + DropDownList2.SelectedItem.ToString() + "')group by m.fyyear, m.pcode, y.salary, y.ta,y.contigency,y.nrc,y.institcharges,y.others,y.yearlyalloc", con);